SUMIF with multiple criteria

[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.

 

[Marcelo]

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

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Nan" escreveu:

 

[Dave Peterson]

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

 

[Nan]

Thanks Marcelo! This is exactly what I needed!

 

[Marcelo]

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

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Marcelo" escreveu:

 

[Nan]

Cleaner still! Thank you!
--
TIA, Nan

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

 

[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. 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))

 

[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.

 

[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.

 

[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.

 

[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.

 

[Dave Peterson]

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

<vbg>

 

[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

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.