Sumif with multiple criteria question

[Ambassador]

Given range named "type" and "qty", i am trying to write a formula that will
sum the "qty" of multiple "type"s together. I've tried
=sum(if((type="apples")+(type="oranges"),qty,0) and
=sumif(type,(or("apples"),("oranges")),qty)
neither work....can someone help me please?

Thanks,
Amb

 

[Per Jessen]

Hello

Try this:

=SUMIF(type,"apples",qty)+SUMIF(type,"oranges",qty)

Regards,
Per

 

[T. Valko]

I've tried

=sum(if((type="apples")+(type="oranges"),qty,0)

That one will work if you enter it as an array** and add another closing
parenthesis to the very end.

=SUM(IF((type="apples")+(type="oranges"),qty))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Here's a couple of other ways:

=SUM(SUMIF(type,{"apples","oranges"},qty))

=SUMPRODUCT(--(ISNUMBER(MATCH(type,items,0))),qty)

Where items is a named range containing apples, oranges.

 

[Mike Josephson]

Thanks!

Amb

That one will work if you enter it as an array** and add another closing
parenthesis to the very end.

=SUM(IF((type="apples")+(type="oranges"),qty))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Here's a couple of other ways:

=SUM(SUMIF(type,{"apples","oranges"},qty))

=SUMPRODUCT(--(ISNUMBER(MATCH(type,items,0))),qty)

Where items is a named range containing apples, oranges.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks!

Amb

 

[Shane Devenshire]

Hi,

try these

=SUMPRODUCT((A2:A7={"apples","oranges"})*B2:B7)

or better yet, if you enter the items in a horizontal range

=SUMPRODUCT((A2:A7=D1:E1)*B2:B7)

or as an array with the items laid out vertically

=SUMPRODUCT((A2:A7=TRANSPOSE(D12))*B2:B7)

array - press Shift+Ctrl+Enter to enter the formula

Where A2:A7 has your types and B2:B7 the qty. And a list of those items you
wish to sum is in D1:E1.

In 2007

=SUMPRODUCT(SUMIFS(B2:B7,A2:A7,{"apples","oranges"}))

or

=SUMPRODUCT(SUMIFS(B2:B7,A2:A7,G1:G2))

with the list running vertically