IFANDSUM Questions

[JubberB]

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 1	Data 2	Date	Date	Field 1	Field 2	Field 3	Field 4	Field 5	Field 6	Field 7	Field 8	Field 9	Commission	Bonus $50	Bonus $75	Bonus $75	Bonus $100	Bonus Fields Combined
				0	0	0	0	0	0	0	0	0	$ -	$0	$0	$0	$0
				0	0	0	0	0	0	0	0	0	$ -	$0	$0	$0	$0
				0	0	0	0	0	0	0	0	0	$ -	$0	$0	$0	$0
				0	0	0	0	0	0	0	0	0	$ -	$0	$0	$0	$0
				0	0	0	0	0	0	0	0	0	$ -	$0	$0	$0	$0
Grand Total	N/A	N/A	N/A	0	0	0	0	0	0	0	0	0	$ -	$ -	$ -	$ -	$ -

<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

 

[Eric W]

I tried to compress the formula a bit since there are some duplicated comparisons. However, some of the bonuses have overlapping criteria, so you'd still end up with a complicated formula. You might try this:

=MAX(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:F2)>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))

 

[JubberB]

Wow! Nicely done! MUCH APPRECIATED! IOU at least a pint!
JohnB

 

[Eric W]

Glad to help!