IFANDSUM Questions

JubberB

New Member
Joined
Nov 1, 2019
Messages
33
I have 4 =IF(AND(SUM formulas that all work individually, but I would like to get them all to work in one cell. Can this be done?

Here are the formulas:
=IF(AND(SUM(D3:L3)>9,(SUM(D3:L3)<14),SUM(D3:E3)>0,SUM(F3:G3)>0),"$50","$0")
=IF(AND(SUM(D3:L3)>14,SUM(D3:L3)>0,SUM(F3:G3)>0),"$75","$0")
=IF(AND(SUM(D3:L3)>9,SUM(D3:L3)<14,SUM(H3:L3)>0),"$75","$0")
=IF(AND(SUM(D3:L3)>14,SUM(H3:L3)>0),"$100","$0")
here's the sheet with all the cells so the columns and numbers make sense:

Data 1Data 2DateDateField 1Field 2Field 3Field 4Field 5Field 6Field 7Field 8Field 9 Commission Bonus $50 Bonus $75 Bonus $75 Bonus $100 Bonus Fields Combined
000000000 $ - $0 $0 $0 $0
000000000 $ - $0 $0 $0 $0
000000000 $ - $0 $0 $0 $0
000000000 $ - $0 $0 $0 $0
000000000 $ - $0 $0 $0 $0
Grand TotalN/AN/AN/A000000000 $ - $ - $ - $ - $ -
<colgroup><col width="78" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2759;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2417;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1678;" span="2"> <col width="46" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1621;" span="9"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3214;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;" span="3"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="159" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5660;"> <tbody> </tbody>

Thank you for the help!
JohnB
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,772
Welcome to the forum.

A few thoughts: First, it's best to use actual values like 0 and 75 instead of "$0" and "$75" in your formulas. That way you can use them as values and sum them up. Use cell formatting to make it look like $75.

Next, if you do that, then you can put all 4 formulas in the N:Q columns and the combined formula would just be SUM(N3:Q3). If you want to actually combine them and get rid of the N:Q columns, you can just do

=IF(formula1) + IF(formula2) + IF(formula3) + IF(formula4)

I wouldn't recommend leaving the N:Q columns AND using the combined IF. That means you'd have identical formulas in 2 places, and if you ever change them, you'll have to change them in 2 places. Best to leave the N:Q formulas and use a SUM to combine them.


Next thought, you don't have any >= or <= operators. This means if someone has exactly 14 for SUM(D3:L3), they won't get anything.

Lastly, in your second formula,

=IF(AND(SUM(D3:L3)>14,SUM(D3:L3)>0,SUM(F3:G3)>0),"$75","$0")

the part in red is redundant, since we know the part in blue must also be true. I suspect you want SUM(D3:E3)>0 instead based on your first formula.

Hope this helps!
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,782
Office Version
2007
Platform
Windows
Hi @JubberB, welcome to the forum!

I suggest you fill your sheet with examples and verify when it is 0, 50, 75 or 100.
Since reviewing your formulas, 2 conditions may be met, then you will have 50 and 75 or 75 and 100 at the same time.

Review the following example:
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:10.46px;" /><col style="width:10.46px;" /><col style="width:10.46px;" /><col style="width:39.92px;" /><col style="width:39.92px;" /><col style="width:34.22px;" /><col style="width:34.22px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:35.17px;" /><col style="width:62.73px;" /><col style="width:62.73px;" /><col style="width:62.73px;" /><col style="width:69.39px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td style="background-color:#b6dde8; ">Data1</td><td style="background-color:#b6dde8; ">Data2</td><td style="background-color:#b6dde8; ">Date</td><td style="background-color:#b6dde8; ">Date</td><td style="background-color:#ffff00; ">Field1</td><td style="background-color:#ffff00; ">Field2</td><td style="background-color:#ffff00; ">Field3</td><td style="background-color:#ffff00; ">Field4</td><td style="background-color:#ccc0da; ">Field5</td><td style="background-color:#ccc0da; ">Field6</td><td style="background-color:#ccc0da; ">Field7</td><td style="background-color:#ccc0da; ">Field8</td><td style="background-color:#ccc0da; ">Field9</td><td >Com</td><td style="background-color:#92d050; ">Bonus$50</td><td style="background-color:#92d050; ">Bonus$75</td><td style="background-color:#92d050; ">Bonus$75</td><td style="background-color:#92d050; ">Bonus$100</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">8</td><td style="text-align:right; ">1</td><td style="text-align:right; ">3</td><td style="text-align:right; ">1</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td >$-</td><td style="text-align:right; ">$0</td><td style="text-align:right; ">$0</td><td style="text-align:right; ">$0</td><td style="text-align:right; ">$0</td><td style="text-align:right; ">0</td><td style="background-color:#ff0000; color:#ffffff; text-align:right; ">75</td><td style="text-align:right; ">0</td><td style="background-color:#ff0000; color:#ffffff; text-align:right; ">100</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">8</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td >$-</td><td style="text-align:right; ">$0</td><td style="text-align:right; ">$0</td><td style="text-align:right; ">$0</td><td style="text-align:right; ">$0</td><td style="background-color:#ff0000; color:#ffffff; text-align:right; ">50</td><td style="text-align:right; ">0</td><td style="background-color:#ff0000; color:#ffffff; text-align:right; ">75</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>
 

JubberB

New Member
Joined
Nov 1, 2019
Messages
33
Eric,

Just tried it and it worked perfectly! Thanks. Last question regarding combining all results into one cell. I don't know how to get my formulas into the format you suggested: =IF(formula1) + IF(formula2) + IF(formula3) + IF(formula4).

Here are my revised formulas:
=IF(AND(SUM(E2:M2)>=10,(SUM(E2:M2)<=14),SUM(E2:F2)>0,SUM(G2:H2)>0),50,0)
=IF(AND(SUM(E2:M2)>=15,SUM(E2:M2)>0,SUM(G2:H2)>0),75,0)
=IF(AND(SUM(E2:M2)>=10,SUM(E2:M2)<=14,SUM(I2:M2)>0),75,0)
=IF(AND(SUM(E2:M2)>=15,SUM(I2:M2)>0),100,0)

Thanks again for your reply and help!
JohnB
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,772
To combine them they would just be:

=IF(AND(SUM(E2:M2)>=10,(SUM(E2:M2)<=14),SUM(E2:F2)>0,SUM(G2:H2)>0),50,0) +
IF(AND(SUM(E2:M2)>=15,SUM(E2:M2)>0,SUM(G2:H2)>0),75,0) +
IF(AND(SUM(E2:M2)>=10,SUM(E2:M2)<=14,SUM(I2:M2)>0),75,0) +
IF(AND(SUM(E2:M2)>=15,SUM(I2:M2)>0),100,0)

A couple of thoughts though. Is it your intention that a given line can generate multiple bonuses which you want summed, or do you just want the maximum bonus of the options? That would affect how to combine them. Also, the redundant SUM(E2:M2) is still in the second formula. It might be easier to explain in words what your criteria are so we can work from that.
 

JubberB

New Member
Joined
Nov 1, 2019
Messages
33
Eric,

Excellent & TY! The goal is to return the maximum bonus amount if the defined criteria is met. It number should be $50, $75, or $100.

Thanks for much!

JohnB
 

JubberB

New Member
Joined
Nov 1, 2019
Messages
33
I'm also looking to combine these formulas into 1 cell:

=SUMIFS('Adam Sanders'!E10:E29,'Adam Sanders'!$D$10:$D$29,"="&A1)
=SUMIFS('Adam Sanders'!F10:F29,'Adam Sanders'!$D$10:$D$29,"="&A1)
=SUMIFS('Adam Sanders'!G10:G29,'Adam Sanders'!$D$10:$D$29,"="&A1)
=SUMIFS('Adam Sanders'!H10:H29,'Adam Sanders'!$D$10:$D$29,"="&A1)
=SUMIFS('Adam Sanders'!I10:I29,'Adam Sanders'!$D$10:$D$29,"="&A1)
=SUMIFS('Adam Sanders'!J10:J29,'Adam Sanders'!$D$10:$D$29,"="&A1)
=SUMIFS('Adam Sanders'!K10:K29,'Adam Sanders'!$D$10:$D$29,"="&A1)
=SUMIFS('Adam Sanders'!L10:L29,'Adam Sanders'!$D$10:$D$29,"="&A1)
=SUMIFS('Adam Sanders'!M10:M29,'Adam Sanders'!$D$10:$D$29,"="&A1)

Sorry for being a PIA!

JohnB
 

JubberB

New Member
Joined
Nov 1, 2019
Messages
33
This one still has me stuck. Total Bonus should be $50, $75, or $100.
Thanks,
JohnB
 

Watch MrExcel Video

Forum statistics

Threads
1,099,507
Messages
5,469,029
Members
406,628
Latest member
jared92

This Week's Hot Topics

Top