An IMA member recently posed an interesting question: “How can you use AVERAGEIFS when you have two conditions in the same column?” There are various solutions posted online for how to successfully use SUMIFS for two conditions in the same column, but those solutions will not work with AVERAGEIFS.

A Quick History

SUMIF and COUNTIF debuted in Excel 2007. Later, Microsoft added AVERAGEIF in Excel 2013. In that same version, they added plural versions of all three functions, designed to handle conditions in multiple columns: SUMIFS, COUNTIFS, AVERAGEIFS. Much later, in 2017, Microsoft added MINIFS and MAXIFS.

Of the five “*-IFS” functions, four of them can be used with an array constant to handle multiple conditions in the same column. However, the same array constant technique will not work when trying to calculate an average due to the “average of averages” problem in math.

The 2019-era dynamic array function called FILTER can provide a solution.

Conditions in Multiple Columns

The syntax of the AVERAGEIFS function starts with the range of numbers that you want to average. It is then followed by pairs of arguments pointing to a criteria range and a criteria.

All of the *-IFS functions have the requirement that the criteria range must be a range in the grid and cannot be a clever calculation to generate an array on the fly.

Figure 1 shows a formula to average all of the rows where the Name in column A is Andy and the Product in column B is Banana. The AVERAGEIFS function starts by pointing to the values to be averaged. In this case, this is the values in D7:D18.

The first criteria range is the names in A7:A18. The first criteria value is “Andy.”

The second criteria range is the products in B7:B18. The first criteria value is “Banana.”

In Figure 1, the Checker column is a temporary helper column to help illustrate the correct answer. The formula should be averaging the values 1, 8, and 256 for an average of 88.333. This matches the formula in A4: =AVERAGEIFS(D7:D18,A7:A18,"Andy",B7:B18,"Banana").

Figure 1
Figure 1

Figure 2 shows the ranges being used in the formula. While the plural AVERAGEIFS function was added to handle multiple conditions in Excel 2013, it does a great job when those conditions are in two different columns.

Figure 2
Figure 2

However, the question from the IMA member was different. They wanted the average where column B is Apple and where column C is either zero or five.

Figure 3 shows several attempts at building this formula.

In cell A4, a formula successfully finds the average of Apple and Zero with =AVERAGEIFS(D10:D21,B10:B21,"Apple",C10:C21,0).

It is easy to modify that formula to find the average of Apple and Five using this formula in A5: =AVERAGEIFS(D10:D21,B10:B21,"Apple",C10:C21,5).

However, an interesting problem arises when you try to return all of the values from rows that have either zero or five in column C. The solution frequently posted online for SUMIFS with two conditions in one column say to use an array constant with both values. For example, for the criteria range of C10:C21, you would ask for {0,5}.

Look carefully at the results of the formula in cell A6 as shown in Figure 3. When cell A6 is selected, it is showing that the one formula is returning a spilled array of 34 in A6 and 182 in B6. What is happening here? The 34 is the average of all of the values where the rating is zero. The 182 is the average of all the values where the rating is 5. In cell A7, when you attempt to take the average of the two values returned by AVERAGEIFS, you get the wrong answer of 108 because you can’t take the average of averages. Based on the Checker column, the true average is 122.8.

In Figure 3, the 108 shown in A7 is the average of 182 and 34. This is not the correct answer shown as the average of the Checker column. The correct answer is 122.8.

Figure 3
Figure 3

If you hoped to use the array constant solution, you would have to figure out the SUMIFS of the matching values and then divide by the COUNTIFS of the matching values. This formula becomes very cumbersome, as shown in Figure 4.

Figure 4 shows the intermediate calculations. In A4, the SUM of the SUMIFS correctly adds the five matching values. In A5, the SUM of the COUNTIFS correctly finds that five values were matching. Finally, in A6, a formula divides the SUMIFS by the COUNTIFS to arrive at the correct answer.

Figure 4
Figure 4

Luckily, the Excel team introduced a new class of functions to Microsoft 365 in September 2019. Known as dynamic array functions, these formulas are able to work with arrays of results in memory.

The FILTER function is one of these dynamic arrays.

The syntax of FILTER is the range of numbers, and then a calculation that shows which of those numbers to include.

In Figure 5, the range of numbers is in D10:D21. The formula in A4 checks for the rows where B10:B21 is Apple and C10:C21 is zero. Notice that each of the conditions are wrapped in parentheses and the conditions are multiplied together. =AVERAGE(FILTER(D10:D21,(B10:B21="Apple")*(C10:C21=0))).

In this formula, think of the multiplication sign as an “AND” operation.

In a similar fashion, the formula in A5 finds the average of values where the product is Banana and the rating is 5.

But the formula in A6 introduces a new operator. If the multiplication sign is used for the AND operation, the plus sign is used as an OR operation. (C10:C21=0)+(C10:C21=5) returns True for the rows where the Rating is 1 or 5.

Because Excel will perform multiplication before addition, it is important to wrap the two OR conditions in another set of parentheses: ((C10:C21=0)+(C10:C21=5)). Finally, multiply the two OR conditions by the test to see if the product is Apple. The formula in A6 is =AVERAGE(FILTER(D10:D21,(B10:B21="Apple")*((C10:C21=0)+(C10:C21=5)))). The result of 122.8 is the correct result shown in the Checker column.

Figure 5
Figure 5

To help you visualize what is happening in the FILTER function, Figure 6 shows that one FILTER function is returning a spilled array with 5 rows and 1 column.

Figure 6
Figure 6

After using FILTER and other dynamic array functions for a while, I find myself becoming more frustrated that the older *-IFS functions are limited to only working with a range in the grid and cannot work with arrays calculated on the fly.

In this case, the flexibility of FILTER provides a great workaround for any situation where you need an average based on two conditions in the same column.


About the Authors