Too many IF statements

[shane24]

I have a worksheet that helps me price out deals based on different charts. There are different portions of the deal that use different charts. I use the same formula throughout the different pieces. I have removed all the different parts to make the attached worksheet easier to ask for help with.

I currently use IF statements (1 IF statement for each chart) to calculate what part of the rate chart to use based on the total price of the deal. Prior to now, I only had 6 charts. Now, the company I work for has 8 charts. That pushes me just over the top of the allowed 7 IF statements in one formula in Excel.

The IF statements in this example are in cells E23 & F23. They reference cells D23, D11 and I22/K22 to provide the specific figure I need in the various charts.

Could someone please take a look at the sample file that I have attached and tell me if there is a chance we can use a different formula? I have put comments throughout the worksheet to hopefully make it easy to understand.


All help is greatly appreciated

Thanks,
Shane

[Special-K]

Try this, I've split out the function so you can see what I've done.

In E23

=($C$23="I")*SUMPRODUCT((E$22=$AE$6:$AE$42)*($D$11>=$AF$3:$AL$3)*($D$11<=$AF$4:$AL$4)*($AF$6:$AL$42))+

($C$23="II")*SUMPRODUCT((E$22=$AN$6:$AN$42)*($D$11>=$AO$3:$AS$3)*($D$11<=$AO$4:$AS$4)*($AO$6:$AS$42))+

($C$23="III")*SUMPRODUCT((E$22=$AU$6:$AU$42)*($D$11>=$AV$3:$AZ$3)*($D$11<=$AV$4:$AZ$4)*($AV$6:$AZ$42))+

($C$23="IV")*SUMPRODUCT((E$22=$BB$6:$BB$42)*($D$11>=$BC$3:$BG$3)*($D$11<=$BC$4:$BG$4)*($BC$6:$BG$42))+

($C$23="V")*SUMPRODUCT((E$22=$BI$6:$BI$42)*($D$11>=$BJ$3:$BP$3)*($D$11<=$BJ$4:$BP$4)*($BJ$6:$BP$42))+

($C$23="VI")*SUMPRODUCT((E$22=$BR$6:$BR$42)*($D$11>=$BS$3:$BW$3)*($D$11<=$BS$4:$BW$4)*($BS$6:$BW$42))+

($C$23="XYZ")*SUMPRODUCT((E$22=$X$6:$X$42)*($D$11>=$Y$3)*($D$11<=$Y$4)*($Y$6:$Y$42))

In F23

($C$23="I")*SUMPRODUCT((F$22=$AD$6:$AD$42)*($D$11>=$AE$3:$AK$3)*($D$11<=$AE$4:$AK$4)*($AE$6:$AK$42))+

($C$23="II")*SUMPRODUCT((F$22=$AM$6:$AM$42)*($D$11>=$AN$3:$AR$3)*($D$11<=$AN$4:$AR$4)*($AN$6:$AR$42))+

($C$23="III")*SUMPRODUCT((F$22=$AT$6:$AT$42)*($D$11>=$AU$3:$AY$3)*($D$11<=$AU$4:$AY$4)*($AU$6:$AY$42))+

($C$23="IV")*SUMPRODUCT((F$22=$BA$6:$BA$42)*($D$11>=$BB$3:$BF$3)*($D$11<=$BB$4:$BF$4)*($BB$6:$BF$42))+

($C$23="V")*SUMPRODUCT((F$22=$BH$6:$BH$42)*($D$11>=$BI$3:$BO$3)*($D$11<=$BI$4:$BO$4)*($BI$6:$BO$42))+

($C$23="VI")*SUMPRODUCT((F$22=$BQ$6:$BQ$42)*($D$11>=$BR$3:$BV$3)*($D$11<=$BR$4:$BV$4)*($BR$6:$BV$42))+

($C$23="XYZ")*SUMPRODUCT((F$22=$X$6:$X$42)*($D$11>=$Y$3)*($D$11<=$Y$4)*($Y$6:$Y$42))

All you have to do now is set up IF statements to say IF the result of this is 0 then there's "No Data for this Chart"
(You can copy these formulas straight into the worksheet)

[shane24]

Thank you very much for your quick response. That worked perfectly.

I left out the IF result is 0 statement. I am having a brain fart on the it. If you have a moment to send that over, I would appreciate it.

Thank you very much!!

Shane