'Sumif with multiple ranges

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

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

I prepared this sheet if that helps. https://docs.google.com/spreadsheets/d/1rdt74GNKPtVpnIpMVNCDZ8hWTqZA1P9dq7ZX7Z6-5SU/edit#gid=0

Hoping to learn from you! Kind regards

Thanks for the insightful answers :)

google-sheets google-sheets-formula

Solution 1:^[1]

I recommend this approach:

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

(See my newly added sheet in your sample spreadsheet.)

Solution 2:^[2]

This is a limitation of the SUMIF function. When working with arrays you can use SUM(FILTER()) instead:

=sum(filter({A3:A5;D3:D5},{B3:B5;E3:E5}="Yes"))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Erik Tyler
Solution 2	ztiaa

'Sumif with multiple ranges

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]