Sum a cell *RANGE* based on 2 criteria

[HURTMYPONY]

Hello,

Without adding an additional "total" column, is there a way, in a single formula, to sum a RANGE of cells in a single row, based on 2 criteria?

I know SUMIFS will total a single column based on multiple criteria, but can it sum multiple cells in the same row?

In other words:

Sum columns W:AP on each row if Column G is equal to B4 *and* Column AR is equal to "1".

I assume this is not possible, and I must add an additional column that sums W:AP and use SUMIFS to deliver that amount.

 

[njimack]

Confirm with ctrl+shift+enter, and replace the 4 and 8 based on the number of rows you need...
=SUM(IF(G4:G8=B4,IF(AR4:AR8=1,W4:AP8)))

 

[BeingYogi]

provide snapshot or data structure plz

 

[barry houdini]

Try SUMPRODUCT like this

=SUMPRODUCT((G2:G1000=B4)*(AR2:AR1000=1)*W2:AP1000)

W2:AP1000 must not contain any text values. If that's a problem you can use an "array formula"

=SUM(IF((G2:G1000=B4)*(AR2:AR1000=1),W2:AP1000))

confirmed with CTRL+SHIFT+ENTER

adjust ranges as required. Note: I assumed that the 1 for column AR is numeric - if not then add quotes, i.e. "1"

 

[HURTMYPONY]

Thank you for the quick reply, I will try this in just a bit.

 

[HURTMYPONY]

Works great! For the record, I added a third criterion and adjusted for the incorrect column names I gave you.

=SUMPRODUCT((Source!I5:I1000=C4)*(Source!AR5:AR1000=1)*(Source!R5:R1000="L")*Source!W5:AP1000)

thank you very much!