sumif multiple criteria

[Chris Cowles]

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.

 

[Barb Reinhardt]

Try something like this for Liquid

=SUMPRODUCT(--(A1:A5="D"),(C1:C5))+SUMPRODUCT(--(A1:A5="E"),(C1:C5))

I tried a more complex IF .. OR equation in the sumproduct and couldn't get
it to work.

HTH,
Barb Reinhardt

 

[Ragdyer]

Try these:

=SUM(SUMIF(A:A,{"A","B","E"},C:C))

=SUM(SUMIF(A:A,{"C","D"},C:C))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

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.

 

[T. Valko]

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

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,{"A","B","E"},0))),C1:C5)

Solid = Sumif(A:A,C or D,C:C). Result=1959

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,{"C","D"},0))),C1:C5)

Note that you can't use *entire* columns as range references (unless you're
using Excel 2007)

As for qoutes around numeric criteria, ie: ">1000", you'd have to ask MS why
they programmed it like that.

Biff

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.

 

[Chris Cowles]

Try these:

=SUM(SUMIF(A:A,{"A","B","E"},C:C))

=SUM(SUMIF(A:A,{"C","D"},C:C))

It does, and looks elegant. What's the effect of the braces, if the outer SUM() were not there?

 

[Chris Cowles]

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,{"C","D"},0))),C1:C5)

Note that you can't use *entire* columns as range references (unless you're
using Excel 2007)

As for qoutes around numeric criteria, ie: ">1000", you'd have to ask MS why
they programmed it like that.

Biff

I'm interpreting ISNUMBER to be a trap for non-numeric values?

Is the leading -- required before the (ISNUMBER... ? What is the effect or intent?

 

[Chris Cowles]

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,{"C","D"},0))),C1:C5)

Never mind my repeat question about the double negative. I see that you answered it on another thread.

 

[T. Valko]

I'm interpreting ISNUMBER to be a trap for non-numeric values?

Yes. The MATCH fuction will reurn either a number of an error. ISNUMBER is
used to "filter out" the errors.

Biff

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,{"C","D"},0))),C1:C5)

Note that you can't use *entire* columns as range references (unless
you're
using Excel 2007)

As for qoutes around numeric criteria, ie: ">1000", you'd have to ask MS
why
they programmed it like that.

Biff

I'm interpreting ISNUMBER to be a trap for non-numeric values?

Is the leading -- required before the (ISNUMBER... ? What is the effect or
intent?

 

[RagDyeR]

If you remove the SUM() function from the formula, you'll see that the
Sumif() formula itself will return *only* the results of using the *first*
criteria ( "A" ) in the calculations.

Now, while you still have the Sumif() formula by itself (without it being
wrapped in the SUM() function) , select the entire formula in the formula
bar, and then hit <F9>.
You'll now see in the formula bar, the actual 3 individual results of the 3
separate criteria.
Hit <Esc> to exit this evaluation mode without changing the formula.

So, the SUM() function is doing what it's meant to do, totaling the
individual returns.


As fat as the curly braces:

In this case, the curly braces are called an "Array Constant".

They sort of permit a formula to reference an array of values, without the
necessity of the formula actually functioning as an array formula.

Lets enter your criteria (constants) for Liquid in say Column J:
J1 = A
J2 = B
J3 = E

Now, let's take this array of constants, and use them in the "unwrapped"
Sumif() formula:

=SUM(SUMIF(A:A,J1:J3,C:C))

What you now see returned is a 0, even though you will *still* see the 3
separate results in the formula bar if you use <F9>.

BUT, we can make it an *array* formula:
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE, we now get the return of the first criteria, just as we did
in the "unwrapped" formula version that used the array constant.

You can now add the SUM() function:

=SUM(SUMIF(A:A,J1:J3,C:C))

and use *CSE* to make it an array formula, and you'll get the same results
as the original *NON*-array formula!


Another example of array constants can be in this IF() formula:

=IF(OR(A1={"A","B","E"}),"LIQUID","Solid")


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Try these:

=SUM(SUMIF(A:A,{"A","B","E"},C:C))

=SUM(SUMIF(A:A,{"C","D"},C:C))

It does, and looks elegant. What's the effect of the braces, if the outer
SUM() were not there?

 

[Teethless mama]

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