Forum Discussion

gminii's avatar
gminii
Copper Contributor
Aug 03, 2019
Solved

Multiple nested IF formula in a table with calculations

Hi! I am a newbie in excel and am trying to create a multiple nested IF formula to calculate a price based on weight for different metals for jewelry.   If column 1 is Brass, multiply column 2 (gr...
excel
  • Yury Tokarev's avatar
    Yury Tokarev
    Aug 03, 2019

    Hi gminii ,

     

    I would not recommend using nested IFs, as they make formula complex. Instead, I suggest using SUMPRODUCT. If the names of the columns 5 to 9 were 'BR', 'SS', 'PG', 'RG' and 'WG' respectively, and, assuming that the name of the table is 'MyTable', your formula would be =SUMPRODUCT((MyTable[[#Headers],[BR]:[WG]]=[@Column1])*MyTable[@[BR]:[WG]]*[@Column2])

     

    This should also solve your issue with formatting

     

    Thanks

    Yury

Yury Tokarev's avatar
Yury Tokarev
Iron Contributor
Aug 03, 2019

Hi gminii ,

 

I would not recommend using nested IFs, as they make formula complex. Instead, I suggest using SUMPRODUCT. If the names of the columns 5 to 9 were 'BR', 'SS', 'PG', 'RG' and 'WG' respectively, and, assuming that the name of the table is 'MyTable', your formula would be =SUMPRODUCT((MyTable[[#Headers],[BR]:[WG]]=[@Column1])*MyTable[@[BR]:[WG]]*[@Column2])

 

This should also solve your issue with formatting

 

Thanks

Yury

  • gminii's avatar
    gminii
    Copper Contributor
    Aug 03, 2019

    Thank you for your quick respone Yury Tokarev 

    I tried your solution but it results in #VALUE!

    Any idea what could be the cause of this?

Resources