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

alderman

New member
Joined
Jan 23, 2014
Messages
19
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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
Joined
Jan 23, 2014
Messages
19
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
I don't see how the number of lines affects it.
 

razisartaj

New member
Joined
May 25, 2014
Messages
7
Reaction score
0
Points
0
Hey
Can u give me it on Excel Please?
tell me what exactly do you want?

Hope for your reply
 

alderman

New member
Joined
Jan 23, 2014
Messages
19
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 
Top