Multiple criterias checking... is it possible using if(countifs())?

alderman

New member
What I am looking for is a method to check from a long list if from the same Number set, if the Category D has greater days than any other higher category (A, B, C). I have been tinkering with an if(Countifs()) equation, but I cannnot get it to work.

So in the data set below I would like a fourth column that would say Yes/No. In the first dat set below it would be a Yes result as there is a D with more Days than a B.
 Number Cat Days CS140012 B 1562.51 CS140012 B 471.96 CS140012 C 1695.88 CS140012 D 557.14 CS140012 D 517.41 CS140012 D 237.61 CS140012 E 0.00 CS140012 E 0.00

In the next data set the result would be "no" as there are no D's
 Num Cat Days CD140014 B 3450.00 CD140014 B 2315.71 CD140014 B 1434.57 CD140014 B 1376.97 CD140014 B 1140.36 CD140014 C 8070.00 CD140014 C 4594.23 CD140014 C 210.44 CD140014 E 0.00 CD140014 F 0.00

In this next data set the answer would be no, as the D has less days than the C's.
 Num Cat Days CD140015 C 870.00 CD140015 C 319.60 CD140015 D 318.40 CD140015 E 0.00 CD140015 E 0.00

Any suggestions?

Bob Phillips

Super Moderator
Staff member
Try this array formula

=IF(MAX(IF(B2:B9="D",C2:C9))>MIN(IF((B2:B9<>"D")*(C2:C9<>0),C2:C9)),"yes","no")

alderman

New member
Thanks for the attempt Bob,

My complete list is over 10k lines of data so it is impossible to use set cell references. What I need in the end is to check the same number in the first column and if there is a "D" compare the number of days of the A/B/C and if the nmber of days of the D is larger than any of the A/B/C's, which is why I was toying with the countifs equation.

Bob Phillips

Super Moderator
Staff member
I don't see how the number of lines affects it.

razisartaj

New member
Hey
Can u give me it on Excel Please?
tell me what exactly do you want?

alderman

New member
Hi Bob,

Your equation works for one unique set of column A, for example your reference to B2:B9 is only for CS140012 from column A. My real se of data contains over 10k lines of dat with several hundred unique sets of data in column A (I have suppled 3 examples). If I were to use your equation I would have to go to each set and create another equation for that set. Imagine my three data sets all together. For the second example I would need =IF(MAX(IF(B10:B19="D",C10:C19))>MIN(IF((B10:B19<>"D")*(C10:C19<>0),C10:C19)),"yes","no"). If I am going to do that I might as well just look at each data set manually. I can't use B2:B9998 and C2:C9998 as that would assume one data set and not the several hundred I have.

I hope I explained this well enough.

Bob Phillips

Super Moderator
Staff member
If it's all in blocks of 10 rows, you could have a formula that copies down okay. If the blocks are not that structured, you would need to know what constitutes the start of the next block, a way of determining that in a formula.