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

Question

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

Answer 1

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,""))))

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.

Answer 2

IFS returns a result for both the array inputs of {"Carcass","Benchtop"}.

For instance =IFS(B4:B8={"Carcass","Benchtop"},1) returns 1 for the TRUE and an error for the FALSE, because IFS doesn't behave like IF, where we can decline a value in case of FALSE

Also you use OR on an array, but OR will check any value of the range to return a TRUE (not only in the current row), and will return TRUE for all or FALSE for all.

You can wrap your conditions in MMULT to check for a sum of the boolean and use IF instead of IFS:

=IF(MMULT(N(B4:B20={"Carcass","Benchtop"}),{1;1}),
    (E4:E20 * F4:F20 * G4:G20)/1000000,
     IF(MMULT(N(B4:B20={"Misc","Doors"}),{1;1}),
        G4:G20,
        "")
    )

You could also use:

=IF((B4:B20="Carcass")+(B4:B20="Benchtop"),
    (E4:E20 * F4:F20 * G4:G20)/1000000,
    IF((B4:B20="Misc")+(B4:B20="Doors"),
       G4:G20,
       "")
    )

And as Mayukh commented, if you want to include any non-blanks other than you can use:

=IF((B4:B20="Carcass")+(B4:B20="Benchtop"),(E4:E20 * F4:F20 * G4:G20)/1000000,IF((B4:B20<>""),G4:G20,""))

Answer 3

First approach:

=SWITCH(TRUE,OR(B4={"Carcass","Benchtop"}),(E4*F4*G4)/1000000,OR(B4<>{"Carcass","Benchtop"}),G4,"")

Answer 4

=LET(
     n, MMULT(N({"Carcass","Benchtop"}<>B4:B20),{1;1}),
     IF(n=1,(E4:E20*F4:F20*G4:G20)/10^6,IF(n>1,G4:G20,"")))