| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with multiple criteria
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. -- TIA, Nan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
=SUMIF(a1:a10,"oranges",b1:b10)+SUMIF(a1:a10,"Appl es",b1:b10)
hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Nan" escreveu: 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. -- TIA, Nan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
One mo
=SUM(SUMIF(A1:A10,{"apples","oranges"},B1:B10)) Nan wrote: 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. -- TIA, Nan -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
Thanks Marcelo! This is exactly what I needed!
-- TIA, Nan "Marcelo" wrote: =SUMIF(a1:a10,"oranges",b1:b10)+SUMIF(a1:a10,"Appl es",b1:b10) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Nan" escreveu: 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. -- TIA, Nan |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
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: =SUMIF(a1:a10,"oranges",b1:b10)+SUMIF(a1:a10,"Appl es",b1:b10) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Nan" escreveu: 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. -- TIA, Nan |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
Cleaner still! Thank you!
-- TIA, Nan "Dave Peterson" wrote: One mo =SUM(SUMIF(A1:A10,{"apples","oranges"},B1:B10)) Nan wrote: 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. -- TIA, Nan -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
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. "Marcelo" wrote: =SUMIF(a1:a10,"oranges",b1:b10)+SUMIF(a1:a10,"Appl es",b1:b10) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Nan" escreveu: 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. -- TIA, Nan |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
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)) -- Biff Microsoft Excel MVP "ivan" wrote in message ... 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. "Marcelo" wrote: =SUMIF(a1:a10,"oranges",b1:b10)+SUMIF(a1:a10,"Appl es",b1:b10) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Nan" escreveu: 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. -- TIA, Nan |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
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" wrote: One mo =SUM(SUMIF(A1:A10,{"apples","oranges"},B1:B10)) Nan wrote: 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. -- TIA, Nan -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
See replies to your post in the misc. group.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "RollieG" wrote in message ... 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" wrote: One mo =SUM(SUMIF(A1:A10,{"apples","oranges"},B1:B10)) Nan wrote: 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. -- TIA, Nan -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
One way:
=SUMPRODUCT(SUMIF(A1:A10,c1:d1,B1:B10)) RollieG wrote: 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" wrote: One mo =SUM(SUMIF(A1:A10,{"apples","oranges"},B1:B10)) Nan wrote: 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. -- TIA, Nan -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
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 ! ----------------------------------------------------------------------------------------------- "Dave Peterson" wrote in message ... One way: =SUMPRODUCT(SUMIF(A1:A10,c1:d1,B1:B10)) RollieG wrote: 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" wrote: One mo =SUM(SUMIF(A1:A10,{"apples","oranges"},B1:B10)) Nan wrote: 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. -- TIA, Nan -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
You mean the one time I actually read the message, it's not what the OP really
wants?????? <vbg RagDyeR wrote: 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 ! ----------------------------------------------------------------------------------------------- "Dave Peterson" wrote in message ... One way: =SUMPRODUCT(SUMIF(A1:A10,c1:d1,B1:B10)) RollieG wrote: 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" wrote: One mo =SUM(SUMIF(A1:A10,{"apples","oranges"},B1:B10)) Nan wrote: 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. -- TIA, Nan -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF with multiple criteria
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" wrote in message ... 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" wrote: One mo =SUM(SUMIF(A1:A10,{"apples","oranges"},B1:B10)) Nan wrote: 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. -- TIA, Nan -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF multiple criteria | Excel Worksheet Functions | |||
| SumIf with Multiple Criteria | Excel Worksheet Functions | |||
| SUMIF for Multiple Criteria | Excel Worksheet Functions | |||
| sumif for multiple criteria | Excel Discussion (Misc queries) | |||
| SUMIF multiple criteria | Excel Discussion (Misc queries) | |||
Linear Mode
