CODEWITHSUNDEEP
excelexcel-formulasumcriteriasumifs
Bilk0
Bilk0

Reputation: 1

SUMIFS with multiple criteria and OR

I have seen plenty of posts for SUMIFS with or, but all the examples have a single criteria and an OR. This is usually done with an array like this:

=SUM(SUMIFS(sum_range,criteria_range,{"red","blue"}))

I need to be able to do that, but I need it to have multiple AND criteria and the OR using array or something similar Like this: 

=SUM(SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,{"red","blue"}))

But it doesn't seem to work.

Help please!

Upvotes: 0

Views: 1592

Answers (1)

Fran Reneses
Fran Reneses

Reputation: 29

I'm showing you how it works with an example

Have the following table starting in A1:

a|0|1
a|1|2
b|0|1
b|1|2
b|0|1
c|0|1
c|0|1

Then you try this to sum values in the third column (C) where first column (A) is either "a" or "b" (result is 7):

=SUM(SUMIFS(C1:C7, A1:A7, {"a","b"}))

Then try this to sum values in the third column (C) where first column (A) is either "a" or "b" AND the value in second column (B) is 0 (result is 3):

=SUM(SUMIFS(C1:C7, B1:B7, 0, A1:A7, {"a","b"}))

Note that since these formulas contain an array, they are already array formulas. You don't need to force an array formula by using [ctrl]+[shift]+[enter].

Upvotes: 2

Related Questions