IFANDSUM Questions

J

JubberB

New Member
Joined
Nov 1, 2019
Messages
39
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:

[TABLE="width: 914"]
<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>[TR]
[TD="width: 78, bgcolor: transparent"]Data 1[/TD]
[TD="width: 68, bgcolor: transparent"]Data 2[/TD]
[TD="width: 47, bgcolor: transparent"]Date[/TD]
[TD="width: 47, bgcolor: transparent"]Date[/TD]
[TD="width: 46, bgcolor: yellow"]Field 1[/TD]
[TD="width: 46, bgcolor: yellow"]Field 2[/TD]
[TD="width: 46, bgcolor: yellow"]Field 3[/TD]
[TD="width: 46, bgcolor: yellow"]Field 4[/TD]
[TD="width: 46, bgcolor: #92D050"]Field 5[/TD]
[TD="width: 46, bgcolor: #92D050"]Field 6[/TD]
[TD="width: 46, bgcolor: #92D050"]Field 7[/TD]
[TD="width: 46, bgcolor: #92D050"]Field 8[/TD]
[TD="width: 46, bgcolor: #92D050"]Field 9[/TD]
[TD="width: 90, bgcolor: transparent"] Commission [/TD]
[TD="width: 79, bgcolor: #92D050"] Bonus $50 [/TD]
[TD="width: 79, bgcolor: #92D050"] Bonus $75 [/TD]
[TD="width: 79, bgcolor: #92D050"] Bonus $75 [/TD]
[TD="width: 86, bgcolor: #92D050"] Bonus $100 [/TD]
[TD="width: 159, bgcolor: yellow"] Bonus Fields Combined [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: transparent"] $ - [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: transparent"] $ - [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: transparent"] $ - [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: transparent"] $ - [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: transparent"] $ - [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: #92D050"] $0 [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: black"]Grand Total[/TD]
[TD="bgcolor: black"]N/A[/TD]
[TD="bgcolor: black"]N/A[/TD]
[TD="bgcolor: black"]N/A[/TD]
[TD="bgcolor: black"]0[/TD]
[TD="bgcolor: black"]0[/TD]
[TD="bgcolor: black"]0[/TD]
[TD="bgcolor: black"]0[/TD]
[TD="bgcolor: black"]0[/TD]
[TD="bgcolor: black"]0[/TD]
[TD="bgcolor: black"]0[/TD]
[TD="bgcolor: black"]0[/TD]
[TD="bgcolor: black"]0[/TD]
[TD="bgcolor: black"] $ - [/TD]
[TD="bgcolor: black"] $ - [/TD]
[TD="bgcolor: black"] $ - [/TD]
[TD="bgcolor: black"] $ - [/TD]
[TD="bgcolor: black"] $ - [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
</tbody>[/TABLE]

Thank you for the help!
JohnB
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
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:
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

HappyChappy
  • Solved
tidying up formulas
Replies
4
Views
322
HappyChappy
HappyChappy
K
  • Solved
Average Based on Criteria
Replies
3
Views
376
Fluff
Fluff
S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
1K
Dave Patton
D
M
  • Solved
Adding a NR to a formula & changing an IF to an IFS statement.
Replies
3
Views
290
Marvo
M
A
  • Solved
Problem with multiple IFS, IF and AND in long formula
Replies
2
Views
447
arendberg
A

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top