Is there a way to use the SUMIFS formula and include an OR criteria. As an example, if I wanted to SUM any cell in column B if in column A the corresponding row contained the criteria of the number 10 or 20 is it possible? The only way I can figure out how to currently do it is two SUMIF formula's added together with each separate criteria. I know you can accomplish this by using a SUMPRODUCT formula was wondering if it can be done using SUMIFS. Thanks in advance for any help.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
SUMIFS Formula with OR Criteria
- Thread starter Weasel
- Start date
Ron Coderre
MrExcel MVP
- Joined
- Jan 14, 2009
- Messages
- 2,316
Here are a few options using regular formulas...
Embedding the criteria:
=SUM(SUMIFS(B1:B10,A1:A10,{10,20}))
or
=SUM(SUMIF(A1:A10,{10,20},B1:B10))
Referencing the criteria:
F1: 10
F2: 20
=SUMPRODUCT(SUMIFS(B1:B10,A1:A10,F1:F2))
or
=SUMPRODUCT(SUMIF(A1:A10,F1:F2,B1:B10))
EDITED TO INCLUDE THIS NON-SUMIF(S) ALTERNATIVE:
=SUMPRODUCT((COUNTIF(F1:F2,A1:A10)>0)*B1:B10)
Does that help?
Embedding the criteria:
=SUM(SUMIFS(B1:B10,A1:A10,{10,20}))
or
=SUM(SUMIF(A1:A10,{10,20},B1:B10))
Referencing the criteria:
F1: 10
F2: 20
=SUMPRODUCT(SUMIFS(B1:B10,A1:A10,F1:F2))
or
=SUMPRODUCT(SUMIF(A1:A10,F1:F2,B1:B10))
EDITED TO INCLUDE THIS NON-SUMIF(S) ALTERNATIVE:
=SUMPRODUCT((COUNTIF(F1:F2,A1:A10)>0)*B1:B10)
Does that help?
Last edited:
Upvote
0
jeffreybrown
Well-known Member
- Joined
- Jul 28, 2004
- Messages
- 5,152
How about...
=SUMPRODUCT(SUMIFS(B:B,A:A,{10,20}))
=SUMPRODUCT(SUMIFS(B:B,A:A,{10,20}))
Upvote
0
