conditional sum ifs - help please

**Helal** · 2013-03-12, 09:55 PM

I have my data arranged as:
Name1, Name2, TotalCount (please see attached file). I need to populate Columns D thru G with following logic:

ColumD (Name1>100&Name2<25): if sum of totalcount over Name1> 100 and sum of totalcount over name2 < 25, then 1 otherwise 0. Desired outcome in ColumD.

ColumE (Name1<100&Name2<25): if sum of totalcount over Name1< 100 and sum of totalcount over name2 < 25, then 1 otherwise 0. Desired outcome in ColumE.
Note: This is the same as ColumnD with < 100

ColumF (Name1>100&Name2<25): if sum of totalcount over Name1> 100 and min of totalcount over name2 < 25, then 1 otherwise 0. Desired outcome in ColumF.
good example are X7 records. Where sum of totalcoutn > 100 and only first record where Name2=Y15 and totalcount=6 takes the value of 1

ColumG (Name1>100&Name2<25): if sum of totalcount over Name1< 100 and min of totalcount over name2 < 25, then 1 otherwise 0. Desired outcome in ColumG.
Note: This is the same as ColumnF with < 100

good example are X4 records where sum of totalcount< 100 but only the second record where Name2=Y6 and totalcount=7 take the value of 1

Hope this is clear as to what I need. I believe the first two columns D& E can be accomplished by:
=AND(C2 < 25, SUMIF($A$2:$A$19, A2, $C$2:$C$19) > 100)+0
=AND(C2 < 25, SUMIF($A$2:$A$19, A2, $C$2:$C$19) < 100)+0

**NBVC** · 2013-03-12, 10:28 PM

Try these formula in D2:G2, respectively:

=AND(SUMIF($B$2:$B$19,$B2,$C$2:$C$19) < 25, SUMIF($A$2:$A$19, $A2, $C$2:$C$19) > 100)+0

=AND(SUMIF($B$2:$B$19,$B2,$C$2:$C$19) < 25, SUMIF($A$2:$A$19, $A2, $C$2:$C$19) < 100)+0

=AND(MIN(IF($B$2:$B$19=$B2,$C$2:$C$19)) < 25, SUMIF($A$2:$A$19, $A2, $C$2:$C$19) > 100)+0

=AND(MIN(IF($B$2:$B$19=$B2,$C$2:$C$19)) < 25, SUMIF($A$2:$A$19, $A2, $C$2:$C$19) > 100)+0

note: the last 2 are array formulas and need to be confirmed with cTRL+SHIFT+ENTER not just ENTER... then copy each formula down.

Now, I am not sure why for the column F, you say that only the first X7 item should return a 1? Why not the second X7 item too? since there is only one Y16 and it is less than 25

**Helal** · 2013-03-13, 05:49 AM

Thanks for the great formulas. The last two are not there yet and it's related to your question. I didn't demonstrate here but my actual data rarely contains common Name2 within Name1 like
X7, Y14
X7, Y15
X7, Y15

in this case your formula may work..I have yet to test it. For the original scenario, I need ONLY the smallest number to take value of 1. Thus, only X, Y15, 6 should take the value of 1 and the rest should be 0. Another word, I need the first occurrence of the smallest numbers take the value of 1. Is this clear?

**NBVC** · 2013-03-13, 02:15 PM

Then perhaps, the F2 formula should be:

=AND(MIN(IF($A$2:$A$19=$A2,$C$2:$C$19)) < 25, $C2=MIN(IF($A$2:$A$19=$A2,$C$2:$C$19)),SUMIF($A$2:$A$19, $A2, $C$2:$C$19) > 100)+0

and G2:

=AND(MIN(IF($A$2:$A$19=$A2,$C$2:$C$19)) < 25, $C2=MIN(IF($A$2:$A$19=$A2,$C$2:$C$19)),SUMIF($A$2:$A$19, $A2, $C$2:$C$19) < 100)+0

each confirmed with CTRL+SHIFT+ENTER and copied down

**Helal** · 2013-03-13, 04:59 PM

Thanks a million...worked like a charm. Any special training to get good at formulas?

**Helal** · 2013-03-13, 05:49 PM

I have one more variation to this...since we are creating this database that is regulatory being updated, I just been asked to add another filed based on this logic:
X7, Y14, 20
X7, Y15, 30
X8, Y15, 60 1
X9, Y15, 20
if sum of Column C over column B > 100, then MAX of Column C = 1 otherwise 0. In this case, since sum of Y15 = 110, then the max which 60 take the value of 1 otherwise 0

Thank You,

Helal

**Helal** · 2013-03-13, 07:04 PM

following your logic, I tried
=AND($C2=MAX(IF($B$2:$B$4,$B2,$C$2:$C$4)),SUMIF($B$2:$B$4,$B2,$C$2:$C$4)>100)+0
with CTRL+SHIFT+ENTER
but did't work!

**NBVC** · 2013-03-13, 09:30 PM

The comparison syntax in the MAX(IF()) formulation uses = to compare, whereas SUMIF uses , to compare...

Try:

=AND($C2=MAX(IF($B$2:$B$4=$B2,$C$2:$C$4)),SUMIF($B$2:$B$4,$B2,$C$2:$C$4)>100)+0