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

1. ## 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.
 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?  Reply With Quote

2. Try this array formula

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

3. 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.  Reply With Quote

4. I don't see how the number of lines affects it.  Reply With Quote

5. Hey
Can u give me it on Excel Please?
tell me what exactly do you want?  Reply With Quote

6. 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.  Reply With Quote

7. 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.  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•