# SUMIF with multiple criteria

N

#### Nan

I'm trying to set up a criteria range using SUMIF.

ColA ColB
apples 1
carrots 2
apples 5
oranges 1
celery 2
apples 1
onions 2
apples 5
oranges 1
celery 2

I hoped something like =SUMIF(A1:A10,(OR("apples","oranges")),B1:B10) would
work, but the result is 0, not 14. Any help is appreciated.

M

#### Marcelo

=SUMIF(a1:a10,"oranges",b1:b10)+SUMIF(a1:a10,"Apples",b1:b10)

hth
--
regards from Brazil
Marcelo

"Nan" escreveu:

D

#### Dave Peterson

One more:
=SUM(SUMIF(A1:A10,{"apples","oranges"},B1:B10))

N

#### Nan

Thanks Marcelo! This is exactly what I needed!

M

#### Marcelo

thanks for the feedback, and check the Dave Peterson tip, it is great

--
regards from Brazil
Marcelo

"Marcelo" escreveu:

N

#### Nan

Cleaner still! Thank you!
--
TIA, Nan

Dave Peterson said:
One more:
=SUM(SUMIF(A1:A10,{"apples","oranges"},B1:B10))

I

#### ivan

Hi :

How about if I have 3 cloumns as follow , then I need to sum the (USA Apple)
sales amount (which is 100+200) in and count the no of occured in the table
(which is 2 times).

Table :
A B C (\$ sales)
USA apple 100
Italy apple 50
USA apple 200
USA orange 10
USA orange 20
Italy orange 50

Thanks.

T

#### T. Valko

Try this:

=SUMPRODUCT(--(A2:A7="USA"),--(B2:B7="apple"),--(C2:C7>=100),--(C2:C7<=200))

Better to use cells to hold the criteria:

E2 = USA
E3 = apple
E4 = 100
E5 = 200

=SUMPRODUCT(--(A2:A7=E2),--(B2:B7=E3),--(C2:C7>=E4),--(C2:C7<=E5))

R

#### RollieG

Instead of {"apples","oranges"} for criteria, can you use something like
{\$C\$1, \$D\$1} ? I am trying to use the criteria to refer to one of two values,
where the values in the cells are strings.

R

#### RagDyeR

See replies to your post in the misc. group.
--

Regards,

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

Instead of {"apples","oranges"} for criteria, can you use something like
{\$C\$1, \$D\$1} ? I am trying to use the criteria to refer to one of two
values,
where the values in the cells are strings.

D

#### Dave Peterson

One way:

=SUMPRODUCT(SUMIF(A1:A10,c1:d1,B1:B10))

Instead of {"apples","oranges"} for criteria, can you use something like
{\$C\$1, \$D\$1} ? I am trying to use the criteria to refer to one of two values,
where the values in the cells are strings.

R

#### RagDyeR

His OP in the misc. group is looking for non-contiguous cells.

Ahhh, the problems of multi-posting!
--

Regards,

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

One way:

=SUMPRODUCT(SUMIF(A1:A10,c1:d1,B1:B10))

Instead of {"apples","oranges"} for criteria, can you use something like
{\$C\$1, \$D\$1} ? I am trying to use the criteria to refer to one of two
values,
where the values in the cells are strings.

D

#### Dave Peterson

You mean the one time I actually read the message, it's not what the OP really
wants??????

<vbg>

A

#### Ashish Mathur

Hi,

Try this

=SUMPRODUCT((A20:A29=A20)+(A20:A29=A23),B20:B29)

A20 holds Apples and A23 holds oranges

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

RollieG said:
Instead of {"apples","oranges"} for criteria, can you use something like
{\$C\$1, \$D\$1} ? I am trying to use the criteria to refer to one of two
values,
where the values in the cells are strings.