• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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
MarblesabcdefghiResult reqd
redwipwipreturnedwipchangedwipreturnedwipacquired0 Days
greenreturnedreturnedreturnedwipreturnedreturnedwipwipreturned0 Days
blackwipwipreturnedwipreturnedwipchangedwipwip2 Days
yellowwipwipreturnedwipwipreturnedwipwipreturned0 Days
whitewipwipwipchangedwipreturnedwipreturnedwip1 Day
bluereturnedwipwipreturnedwip1 Day
amberwipreturnedwipreturnedwipwip2 Days
navyreturnedwipreturnedwipwipwipwip4 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)
 

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
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
MarblesabcdefghiResult reqd
redwipwipreturnedwipwip2 Days
greenreturnedreturnedreturnedwip1 Day
blackwipwipreturnedwip1 Day
yellowwipwipreturnedwip1 Day
whitewipwipwipchanged0 Day
blue0 Day
amberwip1 Day
navywipreturnedwipwip2 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.
Please help on this
 

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))))
MarblesabcdefghjResult reqdFormula output
redwipwipreturnedwipwip2 Days2 Days
greenreturnedreturnedreturnedwip1 Day1 Day
blackwipwipreturnedwip1 Day2 Days
yellowwipwipreturnedwip1 Day2 Days
whitewipwipwipchanged0 Day3 Days
blue0 Day0 Day
amberwip1 Day1 Day
navywipreturnedwipwip2 Days2 Days
 

Peter Bartholomew

Well-Known Member
If you have Excel 365
71551
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:

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'
 

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
 

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
 
Please try

=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

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
 

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"
 
Top