excelexcel-formulaexcel-2021

If Cell has Specific Text then Multiply 3 Cells - Office Pro Plus 2021

I am trying to make a form where if Column B contains "Carcass" or "Benchtop" then Column H will show E * F * G/1000000

But if it shows Misc or Hardware etc, it will show Column G

Otherwise it will return a Blank result if nothing matches.

I've tried multiple different Functions, the most luck I've had was with IF and OR but it doesn't exactly work correctly. This is a function I used that did work until I added more Results to search.

=IFS((B4:B20={"Carcass","Benchtop"}),(E4:E20 * F4:F20 * G4:G20)/1000000,
 OR(B4:B20={"Misc","Doors"}),G4:G20)

I Don't know if this Helps or not, but Functions :D Screenshot 2 enter image description here

Solution

  • Your B4:B20={"Carcass","Benchtop"} returns 2D array that is why you get #SPILL! error.

    Something like this would work:

    =MAP(B4:B20,E4:E20,F4:F20,G4:G20,LAMBDA(b,e,f,g,IF(OR(b={"Carcass","Benchtop"}),e*f*g/1000000,IF(OR(b={"Misc","Doors","Hardware"}),g,""))))

    enter image description here

    Or

    =MAP(B4:B20,E4:E20,F4:F20,G4:G20,LAMBDA(b,e,f,g,IF(OR(b={"Carcass","Benchtop"}),e*f*g/1000000,IF(b<>"",g,""))))

    if you want to do calculation for "Carcass" and "Benchtop", show blank for empty rows and show G for every other B value than "Carcass", "Benchtop" or blank.