More than 64 Nested If Statements

Question

I wrote a formula with 80 nested if statements before realizing that excel has a limit of 64 nested if statements.

There are 80 combinations of criteria, being the Grade and Size of the original and the Grade and Size of the proposed. I would like to enter a quantity in one cell and have it be multiplied by the corresponding factor in the table , based on the criteria I enter, to be shown in a different cell.

Does anyone have any suggestion on how to do this? I am relatively new to excel and have read that I should look into using vlookup. Again, I wouldn't even know how to use it if it did work.

Answer 1

I tend to work to the rule that if there are more than 5 levels of nesting required, there's a better way to do it.

There are more fancy ways to achieve this, but on the basis that each group of four original and conversion grade/size values is unique, you could do it with a simple SUMIFS formula.

See picture below.

Columns A:E represent your conversion table as shown in the picture. Arrange them in one table in rows down the page.

Columns G:K (G3:K3) represent your inputs, so you provide the grade/conversion pairs and a quantity.

The formula in L3 then gets the unique 'Factor' and multiplies by the quantity in K3.

Formula in L3 is =SUMIFS($E$3:$E$6,$A$3:$A$6,G3,$B$3:$B$6,H3,$C$3:$C$6,I3,$D$3:$D$6,J3)*K3

If I've understood your requirements correctly VLOOKUP won't help you in this case, as it cannot do multiple criteria.

Answer 2

SUMIFS Formula worked perfectly! I may use this on some of my other formulas that have a high amount of nested if statements.

Thank you!