SUMIF(A:A,OR("=CRANE", "=EXCAVATOR", "=BOAT"),H:H)
I'm trying to sum field H only if field A = CRANE, EXCAVATOR, BOAT. I don't think I have the OR in the correct place and I have tried several varaions without much luck (keep getting 0) - can somebody help?
Thank You!
SUMIF with OR conditions
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SUMIF with OR conditions
Try this version:
=SUM(SUMIF(A:A,{"CRANE","EXCAVATOR","BOAT"},H:H))
=SUM(SUMIF(A:A,{"CRANE","EXCAVATOR","BOAT"},H:H))
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 89
- Joined: 26 Feb 2013, 01:39
Re: SUMIF with OR conditions
PERFECT! Do the curly brackets act like an OR statement?
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SUMIF with OR conditions
More or less. The curly brackets define an array.
SUMIF(A:A,{"CRANE","EXCAVATOR","BOAT"},H:H) results in 3 separate sums: one for "CRANE", another for "EXCAVATOR" and a third one for "BOAT". The enclosing SUM(...) adds those three together.
SUMIF(A:A,{"CRANE","EXCAVATOR","BOAT"},H:H) results in 3 separate sums: one for "CRANE", another for "EXCAVATOR" and a third one for "BOAT". The enclosing SUM(...) adds those three together.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 89
- Joined: 26 Feb 2013, 01:39
Re: SUMIF with OR conditions
Great to know - thank you for your help!!!!!!
-
- StarLounger
- Posts: 89
- Joined: 26 Feb 2013, 01:39
Re: SUMIF with OR conditions
I'm still working with this formula and I've run across another question.
Given the example above =SUM(SUMIF(A:A,{"CRANE","EXCAVATOR","BOAT"},H:H)), I want to add another condition to this to only sum records where column B:B = Y. I've tried several placements of OR and tried SUMIFS, and I can't get the correct order. Can you help?
Given the example above =SUM(SUMIF(A:A,{"CRANE","EXCAVATOR","BOAT"},H:H)), I want to add another condition to this to only sum records where column B:B = Y. I've tried several placements of OR and tried SUMIFS, and I can't get the correct order. Can you help?
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SUMIF with OR conditions
Try this formula:
=SUM(SUMIFS(H:H,A:A,{"CRANE","EXCAVATOR","BOAT"},B:B,"Y"))
SUMIFS has a different syntax than SUMIF: the sum range must be specified first, then the condition range - condition pairs.
=SUM(SUMIFS(H:H,A:A,{"CRANE","EXCAVATOR","BOAT"},B:B,"Y"))
SUMIFS has a different syntax than SUMIF: the sum range must be specified first, then the condition range - condition pairs.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 89
- Joined: 26 Feb 2013, 01:39
Re: SUMIF with OR conditions
Worked like a charm!! thanks again!