I am facing a problem with excel. I know we can make a SUMIF using multiple values as criteria, but how can we do this when using SUMIFS ? What I mean is ( as you can see at the exampled picture ): I need to get the sum os values when Feature1 is either (a or b) and Feature2 is (m or n) The features need to be setted by Range (in the example ColumnE for feature1 and ColumnF for feature2), not manually The answer should be 2

Reputation: 187

Sumifs with muitiple criteria based on a cell

I am facing a problem with excel. I know we can make a SUMIF using multiple values as criteria, but how can we do this when using SUMIFS ?

What I mean is ( as you can see at the exampled picture ):

I need to get the sum os values when Feature1 is either (a or b) and Feature2 is (m or n)
The features need to be setted by Range (in the example ColumnE for feature1 and ColumnF for feature2), not manually

The answer should be 2

Upvotes: 0

Reputation: 60224

Try (using your {a,b,m,n} filter as an example):

=SUM(ISNUMBER(MATCH(A2:A5,E2:E5,0))*ISNUMBER(MATCH(B2:B5,F2:F5,0)))

Or, in case the amount in the val column might not always be one (1):

=SUM((ISNUMBER(MATCH(A2:A5,E2:E5,0))*ISNUMBER(MATCH(B2:B5,F2:F5,0)))*C2:C5)

Upvotes: 1

Reputation: 14580

You can try to SUM an array search for a or b as a constant requirement

=SUM(SUMIFS(C:C,A:A,{"a","b"},B:B,F2))

Upvotes: 3

Reputation: 23081

Don't know about SUMIFS but you can use an OR condition with SUMPRODUCT

=SUMPRODUCT(((A2:A5="a")+(A2:A5="b"))*(B2:B5="n"))

Upvotes: 1