CODEWITHSUNDEEP
excel
voidsnaze
voidsnaze

Reputation: 19

Check if a value in a range for a specific key is less than / more than a number

I have two columns in Excel. KEY and AMT

KEY         AMT
DATEID_1    200
DATEID_2    6142
DATEID_1    9700
DATEID_2    4500

What I need to work out is for each key - if the sum of that key's amount in the entire table is greater than 10000 - but the individual amounts within the key are ALL less than 10000.

So, supposed output for the above would be

KEY         AMT     FLAG
DATEID_1    200     OK   
DATEID_2    6142    NOT OK 
DATEID_1    9700    OK
DATEID_2    4500    NOT OK

OK for DATEID_1 because 

9700 + 200 = 9900 which is less than 10000 
but each amount less than 10000

NOT OK for DATEID_2 because 

6142 + 4500 = 10642 which is greater than 10000 
but each amount less than 10000

Hope I was clear enough. I can't use VBA.

EDIT : Doesn't work for the below with @Alexis Olson formula.

KEY AMT DATEID_3 26950 DATEID_3 26950 DATEID_4 38000

Upvotes: 0

Views: 60

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

To get this:

KEY         AMT     FLAG
DATEID_1    200     OK   
DATEID_2    6142    NOT OK 
DATEID_1    9700    OK
DATEID_2    4500    NOT OK

You just need to put your conditions together with an AND

=IF(AND(MAX(B:B*(A:A=A2))<10000,SUMIF(A:A,A2,B:B)<10000),"OK","NOT OK")

This takes the maximum value where the KEY matches your current row KEY and checks if it is less than 10000 and uses SUMIF to add up all the AMT with matching KEY and checks if that is less than 10000.

Note that this is an array formula, so it should be entered using Ctrl+Shift+Enter.

This reproduces your given output. However, I'm not sure your stated output is what you meant. Assuming your AMT column contains positive values, then your first condition is redundant since summing to less than 10000 implies each individual one is less than 10000.

Upvotes: 1

Related Questions