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

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.
In the next data set the result would be "no" as there are no D's

In this next data set the answer would be no, as the D has less days than the C's.


Any suggestions?

Try this array formula

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

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.

I don't see how the number of lines affects it.

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

Hope for your reply

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.

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.