• 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

N

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)
 
Hi,

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

Regards
 
yes results are different, both the way it worked out for me, to check the returned & wip. Thank you for the help
 
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
 
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
 
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:
@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'
 
thanks all for the support, but still am not getting the actual output. attached the excel please help
 

Attachments

  • Sample excel.xlsx
    56.9 KB · Views: 6
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
 
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
 
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
 
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

  • Sample excel.xlsx
    22.4 KB · Views: 2
Naveen BN said:
in the excel none of the columns are hidden.
Click to expand...

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 said:
Apologies, its my mistake forgot to save after unhiding the columns. sorry for that
Click to expand...

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
 
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"
 
You must log in or register to reply here.
Back
Top