SUMIF with multiple criteria

  • Thread starter Thread starter Nan
  • Start date Start date
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.
 
=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:
 
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:
 
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.
 
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))
 
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.
 
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.
 
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.
Click to expand...
 
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.
Click to expand...
 
You mean the one time I actually read the message, it's not what the OP really
wants??????

<vbg>
 
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.
Click to expand...
 
Back
Top