Hi,

I have been using SUMPRODUCT to achieve 'tiered' calculations to calculate fees depending on how much has been spent. This works great for me right now however its a very static formula and I have several tables in which I refer to in several nested IF statements which is clunky and Excel file is growing in size

I currently have a cell with data validation drop down list of the different categories (which determines the fee % and ranges) and I would like it that when you select a category from the data validation options, it then looks at the correct fee table and calculates the correct fees.

Currently, I have lots of these nested IF statements in place that look at sheet 'X' for the category list and fee tables:

IF(AND(H7=X!$A$38,F7>0), SUMPRODUCT((F2+G2<=X!$E$39:$E$40)*(F2+G2>X!$D$39:$D$40)*(F2+G2-X!$D$39:$D$40)*X!$F$39:$F$40)+SUMPRODUCT(((F2+G2>X!$E$39:$E$40)*(X!$E$39:$E$40-X!$D$39:$D$40))*X!$F$39:$F$40),

Where X is the sheet name which contains:

- A list of fee categories i.e. Plan A, Plan B etc (e.g. A38 is one of those categories)

- Several tables (like below) each with different tiered fee % and ranges (in picture)

H7 is the cell which contains the drop-down list of options.

F7 Contains the sale cost

What I'd like to be able to do is reduce the number of nested IF Statements I have (over 20) and instead, perhaps use something like VLOOKUP (or some other function), so that when I select the category from the drop-down list, it looks at the selected option and runs the SUMPRODUCT calculation from the relevant fee table.

I hope this makes sense, I've been trying to figure a way to do this but no luck so far.

Thanks!

I have been using SUMPRODUCT to achieve 'tiered' calculations to calculate fees depending on how much has been spent. This works great for me right now however its a very static formula and I have several tables in which I refer to in several nested IF statements which is clunky and Excel file is growing in size

I currently have a cell with data validation drop down list of the different categories (which determines the fee % and ranges) and I would like it that when you select a category from the data validation options, it then looks at the correct fee table and calculates the correct fees.

Currently, I have lots of these nested IF statements in place that look at sheet 'X' for the category list and fee tables:

IF(AND(H7=X!$A$38,F7>0), SUMPRODUCT((F2+G2<=X!$E$39:$E$40)*(F2+G2>X!$D$39:$D$40)*(F2+G2-X!$D$39:$D$40)*X!$F$39:$F$40)+SUMPRODUCT(((F2+G2>X!$E$39:$E$40)*(X!$E$39:$E$40-X!$D$39:$D$40))*X!$F$39:$F$40),

Where X is the sheet name which contains:

- A list of fee categories i.e. Plan A, Plan B etc (e.g. A38 is one of those categories)

- Several tables (like below) each with different tiered fee % and ranges (in picture)

H7 is the cell which contains the drop-down list of options.

F7 Contains the sale cost

What I'd like to be able to do is reduce the number of nested IF Statements I have (over 20) and instead, perhaps use something like VLOOKUP (or some other function), so that when I select the category from the drop-down list, it looks at the selected option and runs the SUMPRODUCT calculation from the relevant fee table.

I hope this makes sense, I've been trying to figure a way to do this but no luck so far.

Thanks!

Last edited by a moderator: