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.

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)

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 :)

CodePudding user response：

I recommend this approach:

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

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

CodePudding user response：

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