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:

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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
135
HappyChappy
HappyChappy
K
  • Solved
Average Based on Criteria
Replies
3
Views
141
Fluff
Fluff
S
  • Question
Formula for Equity Vesting
Replies
6
Views
309
Dave Patton
D
A
  • Solved
Problem with multiple IFS, IF and AND in long formula
Replies
2
Views
123
arendberg
A
M
  • Solved
Please help to code has debug.
Replies
5
Views
117
motilulla
M

Forum statistics

Threads
1,214,667
Messages
6,120,815
Members
448,990
Latest member
rohitsomani

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