Multiple aggregation criteria

A very rugged title, isn’t it? When you use multi-conditional data aggeregation functions like SUMIFS, AVERAGEIFS, COUNTIFS, etc. there may be multiple values ​​from one column that you want to consider. This is accomplished by listing these values ​​in curly brackets and then summing all the results. Here’s a quick guide to how to use multiple values ​​for a one criterion.

If we were to write the formula:

= SUMIFS (D2:D34; B2:B34;”Belgrade”;C2:C34;”red”;C2:C34;”white”)

we would get a score of zero because the last two criteria cancel each other out.

The solution is to set the color criteria as C2: C34 which pulls the values ​​in curly brackets:

{“red”;”white”}

That is, the formula would look like this:

= SUMIFS (D2:D34;B2:B34;”Belgrade”;C2:C34;{“red”; “white”})

This formula returns a value of 20, which again is not the result we want to get because it only takes into account the first criterion. The solution is to “enclose” the existing formula with another SUM function calculate all the results for the values ​​”red” and “white”:

=SUM (SUMIFS (D2:D34;B2:B34;”Belgrade”;C2:C34;{“red”;”white”}))