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

Advertisements

M

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:
 
M

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:
 
Ad

Advertisements

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

Advertisements

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>
 
Ad

Advertisements

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top