SUMIFS, one criteria range, multiple criteria
 

I have the following data

Column A Column B
Amount Account
100 A
50 B
20 C
200 D

I would like to sum items in column A where column B = B or C (I think
these are numbers stored as text).

I cannot get SUMIFS to work with multiple criteria but only one
criteria range even when I use the OR statement.
Can someone help me how to do this please.

thanks

SUMIFS, one criteria range, multiple criteria
 

On Sep 7, 2:09*am, GavinS wrote:
I have the following data

Column A * * Column B
Amount * * * * * *Account
100 * * * * * * * * * *A
50 * * * * * * * * * * B
20 * * * * * * * * * * C
200 * * * * * * * * * D

I would like to sum items in column A where column B = B or C (I think
these are numbers stored as text).

I cannot get SUMIFS to work with multiple criteria but only one
criteria range even when I use the OR statement.
Can someone help me how to do this please.

thanks
Also works with previoius versions of excel
=SUMPRODUCT(--(C1:C4={"a","b"})*B1:B4)

SUMIFS, one criteria range, multiple criteria
 

"GavinS" wrote:
I would like to sum items in column A where column
B = B or C (I think these are numbers stored as text).
I cannot get SUMIFS to work with multiple criteria

=SUMIFS(A1:A100,B1:B100,"B")+SUMIFS(A1:A100,B1:B10 0,"C")

or

=SUMIF(B1:B100,"B",A1:A100)+SUMIF(B1:B100,"C",A1:A 100)

or

=SUMPRODUCT(A1:A100,--(B1:B100={"B","C"}))