# Thread: conditional sum ifs - help please

1. ## conditional sum ifs - help please

Register for a FREE account, and/
or Log in to avoid these ads!

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

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

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

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

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

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

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

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

9. Originally Posted by Helal Thanks a million...worked like a charm. Any special training to get good at formulas?
Yes, read all posts by NBVC on the forums he is member of .  Reply With Quote

10. Originally Posted by Pecoflyer Yes, read all posts by NBVC on the forums he is member of .
LOL    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
•