=SUMPRODUCT((A2:A6={"A","B","E"})*C2:C6)
=SUMPRODUCT((A2:A6={"C","D"})*C2:C6)


"Chris Cowles" wrote:

I've read through some questions and responses and not found what I'm looking for. I want to sum revenue from item numbers. The itemnumbers are grouped into classes. The classes can be further generalized, and I want the sum at that level.

Item numbers are in text format, such as "42", "39", etc.

Classes are a single alpha character, A, B, C, D, E, etc.

Class groups are solid or liquids, e.g., classes A, B and E are solid, C and D are liquid.

I'd like to end up with 2 sum fields, solid and liquid.

Data example:

Class, Itemnumber, Revenue
A, 12, 200
B, 35, 17
C, 550, 1932
D, 192, 27
E, 53, 356

Liquid = Sumif(A:A,A or B or E,C:C). Result = 573
Solid = Sumif(A:A,C or D,C:C). Result=1959

Alternatively, the itemnumbers also follow a pattern. Above C and D are 100. But recall they're text, not numeric, so I assume that should be "100". If I were to sum on that pattern, how can I express it?

What I can't figure out is how to include multiple different criteria into one sumif formula. Your help is appreciated.

Also, can someone expound a bit on why you have to put quotes around criteria? Why can't Excel just accept something like 1000, rather than apparently requiring "1000"? If you're evaluating a number formatted as text, as distinguished by an actual number, I can grasp that. But why is the sign within quotes?

Thanks in advance.