CODEWITHSUNDEEP
excel-formula
Hias
Hias

Reputation: 60

Want SUMIFS to compare two columns row by row

I wasn't sure how to explain it in the title, but here's my problem: I want to sum the values of every row in column A when, in the same row, the value in col B is equal to the value in col C. For example =SUMIFS(A:A,B:B,"="&C2) seems to work, but I don't want to compare to any specific cell, but the C-cell on that specific row, where the potential A-value is.

Edit: I could of course make an additional column with "If B2=C2" and so on by dragging it down, but this is what I want to avoid.

I also need to know the number of rows that is included in the sum.

Upvotes: 1

Views: 7813

Answers (2)

zipa
zipa

Reputation: 27869

I would suggest to limit the ranges but this array formula works:

=SUMPRODUCT(A:A,IF(B:B=C2,B:B))

This must be applied with Ctrl+Shift+Enter.

But now that I read your question again it seems that you just need:

=SUMIFS(A:A,B:B,C2)

Upvotes: 1

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5185

Try:

= SUMPRODUCT((A:A)*((B:B)=(C:C)))

But you should limit the full ranges to where ever your data ends, e.g.

= SUMPRODUCT((A1:A100)*((B1:B100)=(C1:C100)))

Upvotes: 1

Related Questions