# Help required on getting the Count Continuous Non-Blank cells with multiple criteria which appear Last from a row

#### Naveen BN

##### New Member
Help required on getting the text count Continuous Non-Blank cells with multiple criteria which appear Last from a row
 Marbles a b c d e f g h i Result reqd red wip wip returned wip changed wip returned wip acquired 0 Days green returned returned returned wip returned returned wip wip returned 0 Days black wip wip returned wip returned wip changed wip wip 2 Days yellow wip wip returned wip wip returned wip wip returned 0 Days white wip wip wip changed wip returned wip returned wip 1 Day blue returned wip wip returned wip 1 Day amber wip returned wip returned wip wip 2 Days navy returned wip returned wip wip wip wip 4 Days

Please help how to get the result; have tried with below formula but not getting the expected results with multiple criteria.
=IF(COUNTA(B2:J2), LOOKUP(2, 1/(B2:J2<>"returned"), COLUMN(B2:J2)- COLUMN(B2)+1)- IFERROR(LOOKUP(2, 1/(B2:INDEX( B2:J2, LOOKUP(2, 1/( B2:J2<>"returned"), COLUMN(B2:J2)- COLUMN(B2) +1))="returned"), COLUMN(B2:J2)- COLUMN(B2)+1), 0),0)

#### bosco_yip

##### Excel Ninja
Maybe,

In K2, formula copied down :

=9-INDEX(MATCH(1,0/(B2:J2="Returned")),0)&" day"

#### XOR LX

##### Active Member
Hi,

=TEXT(COLUMNS(A2:J2)-MATCH(1,0/(A2:J2<>"wip")),"[=1]0 ""Day"";0 ""Days""")

Regards

#### Naveen BN

##### New Member
Thank you Bosco & Xor for you help. both the formulas worked thank you again for your help

#### XOR LX

##### Active Member
Thank you Bosco & Xor for you help. both the formulas worked thank you again for your help
That's strange, since they give different results!

Regards

#### Naveen BN

##### New Member
yes results are different, both the way it worked out for me, to check the returned & wip. Thank you for the help

#### Naveen BN

##### New Member
Xor need help, from the formula given i tried working around. but facing some problem. the WIP & Returned to be calculated on the consecutive no of times WIP appeared & no of times returned. for example as below
 Marbles a b c d e f g h i Result reqd red wip wip returned wip wip 2 Days green returned returned returned wip 1 Day black wip wip returned wip 1 Day yellow wip wip returned wip 1 Day white wip wip wip changed 0 Day blue 0 Day amber wip 1 Day navy wip returned wip wip 2 Days

from the formula the output is showing as zero days, actual it should be like above table (result reqd). this is tracked for a month.

#### Naveen BN

##### New Member
Have tried with this formula but not giving accurate output. Highlighted in Red is not as expected output, Please help in this
=MAX(FREQUENCY(IF(\$B2:\$J2="wip",COLUMN(\$B2:\$J2)),IF(\$B2:\$J2<>"wip",COLUMN(\$B2:\$J2))))
 Marbles a b c d e f g h j Result reqd Formula output red wip wip returned wip wip 2 Days 2 Days green returned returned returned wip 1 Day 1 Day black wip wip returned wip 1 Day 2 Days yellow wip wip returned wip 1 Day 2 Days white wip wip wip changed 0 Day 3 Days blue 0 Day 0 Day amber wip 1 Day 1 Day navy wip returned wip wip 2 Days 2 Days

#### Peter Bartholomew

##### Well-Known Member
If you have Excel 365

Forgot to say the number format was
[=1]0 "Day"_s;0 "Days"
The extra "_s" was needed to get the numbers, which are right aligned, to line up.

Last edited:

#### Excel Wizard

##### Member

=TEXT(IFNA(MATCH("z",B2:J2)-MATCH(2,1/(B2:INDEX(B2:J2,MATCH("z",B2:J2))<>"wip")),0),"[>1]0 \Da\y\s;0 \Da\y")

#### XOR LX

##### Active Member
Assuming the range in question does not exceed 15 columns:

=TEXT(IF(LOOKUP("ω",A2:J2)="wip",FIND(0,NPV(-0.9,N(A2:J2="wip")))-1,0),"[=1]0 ""Day"";0 ""Days""")

Regards

#### Peter Bartholomew

##### Well-Known Member
@Excel Wizard
You appear to have changed the rules of the game?
My entry for the longest most roundabout solution.
Code:
``````= LET(
event, IF((status<>"")*(status<>"wip"),"action", status),  'Reduce events to common format'
finalEvents, XMATCH({"wip","action"}, event, ,-1),         'Search for final event and wip'
wait, SUM( {1,-1} * finalEvents ),                         'Evaluate wait'
MAX(wait, 0) )                                             'Return positive value'``````

#### Naveen BN

##### New Member
thanks all for the support, but still am not getting the actual output. attached the excel please help

#### Attachments

• 56.9 KB Views: 6

#### XOR LX

##### Active Member
Not sure I understand. Do you realise that your expected results are based on the fact that you've hidden several columns?

As I said, my previous formula will not work on ranges comprising more than 15 columns, though Excel Wizard's formula seems to be giving perfectly good results to me, after unhiding the hidden columns.

Regards

#### Naveen BN

##### New Member
xor understand that it works only upto 15 columns, have attached excel where have highlighted in yellow color are not matching as per expected result, please help

#### Naveen BN

##### New Member
this we track throughout the month from day one to end of month. can you please help me on this

#### XOR LX

##### Active Member
You'll have to explain better. I still don't understand why some columns are hidden. Are these columns to be ignored? If so, on what basis?

Regards

#### Naveen BN

##### New Member
in the excel none of the columns are hidden. this we track on daily basis on wip, it starts from day 1 which will be flashed to the team on daily basis. how many are in wip for how many days
for example: today 11th only one is under wip for 2 days, same on 12th either it will be in WIP or changed, if it is wip then it will be 3 Days else 0 day

#### Excel Wizard

##### Member

=TEXT((LOOKUP("Ω",B2:AF2)="wip")/LOOKUP(2,1/FREQUENCY(IF(B2:AF2="wip",COLUMN(B2:AF2)),IF((B2:AF2<>"")*(B2:AF2<>"Wip"),COLUMN(B2:AF2)))),"[>1]0 \Da\y\s;0 \Da\y")
There is Awaiting confirmation in U7

#### Attachments

• 22.4 KB Views: 2

#### XOR LX

##### Active Member
in the excel none of the columns are hidden.
If we are talking about the file you attached in post #14, that is not true: columns F, G, M, N, T, U, AA and AB are all hidden, and thus being excluded from your desired results.

Regards

#### Naveen BN

##### New Member
Apologies, its my mistake forgot to save after unhiding the columns. sorry for that

#### XOR LX

##### Active Member
Apologies, its my mistake forgot to save after unhiding the columns. sorry for that
But then the results given by Excel Wizard's first formula are all correct, no? You appear to have added your expected results whilst those columns were still hidden.

Regards

#### Naveen BN

##### New Member
even that is not matching with the actual results. actual results in column "AG" is what expected, have highlighted in Yellow color where there is a mismatch with actual results. have given excel wizard's formula in column "AI" and your formula in column "AH"

#### Naveen BN

##### New Member
will rework and get back to you.