Search code examples
google-sheetsgoogle-sheets-formula

SUMIF with multiple ranges

I tried using a union with braces {} and FLATTEN to combine 2 simple ranges as the first input in a SUMIF formula. Neither worked. Summing 2 SUMIF formula works of course but that's not really a solution but a short-term workaround.

  1. Try, doesn't work: 
    =SUMIF({B3:B5;E3:E5},"yes",{A3:A5;D3:D5})
  2. Try, doesn't work: 
    =SUMIF(FLATTEN(B3:B5,E3:E5),"yes",FLATTEN(A3:A5,D3:D5))
  3. Try, works crappily: 
    =SUMIF(B3:B5,"yes",A3:A5)+SUMIF(E3:E5,"yes",D3:D5)
Solution

  • I recommend this approach:

    =SUM(FILTER({A:B;D:E},{B:B;E:E}="yes"))