SUMIFS, one criteria range, multiple criteria

[GavinS]

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

 

[Don Guillett]

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)

 

[joeu2004]

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:B100,"C")

or

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

or

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