Excel’s SUMIF() and COUNTIF() are two of my favorite summarizing functions and we’ve talked about both frequently in this blog. What we haven’t discussed is how to return a conditional average. For instance, using the simple sheet below, you could easily determine the average transaction using =AVERAGE() to evaluate the data in column C.
Now let’s suppose you need to summarize that data by calculating the average number of units sold per region. Let’s look at a couple of ways to return such a conditional average. Depending on your needs, you could use SUMIF() and COUNTIF() to return additional summary information and build on that.
First, you’d enter the criteria. In this case, that’s a unique list of the regions (E3:E8). Next, you’d start summarizing:
F3: =SUMIF($B$2:$B$13,$E3,$C$2:$C$13)
G3: =COUNTIF($B$2:$B$13,$E3)
H3: =F3/G3
Then, you’d copy F3:H3 to create the summary range (F3:H8).
Both functions use the text in column E as the criteria. The SUMIF() function returns the total sold per region; COUNTIF() returns the total transactions per region. The simple expression, F3/G3, used in column H returns the average sold per region. In other words, there were 3 transactions made in the Frankfort region for a total of 244 units sold. The average Frankfort sale is 81.3 units.
A different function
The helper functions are a nice touch as they supply additional information that you might need. But, you could use the AVERAGEIF() to return the same information. Excel’s AVERAGEIF() function uses the same logic and syntax as the SUMIF() and COUNTIF() functions. AVERAGEIF() returns the average of the values within a specific range that meet a specific condition.
This function uses the following syntax:
AVERAGEIF(range, criteria, [avgrange])
where range identifies the cells that must meet critieria, and avgrange identifies the cells you want to average. This function ignores Boolean values and empty cells.
Whether you use a simple expression or AVERAGEIF() doesn’t matter. Neither solution shown is superior (or faster) than the other. In addition, don’t forget about Excel’s pivot table feature – if you don’t mind losing the detail records, a pivot table is a quick way to summarize data.
Two example Excel demonstration files are also available.
