Using Google Sheets, I'm trying to sum two different, non-contiguous ranges based on and/or logic.
My preference is to refer to the conditions by cell reference, e.g.:=SUM(ARRAYFORMULA(SUMIF(E3:E9, B2:B4, D3:D9))+SUM(ARRAYFORMULA(SUMIF(B13:B16, B2:B4, A13:A16))))
I'd also be happy to just do something like this:=SUM(ARRAYFORMULA(SUMIF(E3:E9, {"ca";"cc";"ck"}, D3:D9))+SUM(ARRAYFORMULA(SUMIF(B13:B16, {"ca";"cc";"ck"}, A13:A16))))
Unfortunately, neither of these approaches gives me the correct answer.
This is the formula that gives me a correct answer when using Excel:=SUM(SUMIF(E3:E9, {"ca","cc","ck"}, D3:D9))+SUM(SUMIF(B13:B16, {"ca","cc","ck"}, A13:A16))
I've set up a test Google Sheet here: SUMIF, Array, Multiple Ranges, Multiple Conditions
Ideas?
