The standard AVERAGE function in Microsoft Excel is great for calculating a simple mean. If you highlight a column of 50 test scores, it will quickly tell you the overall class average.
However, what if you have a massive spreadsheet containing test scores from three different schools, and you only want to find the average score for one specific school? Instead of manually sorting the data and copying it to a new sheet, you can use the highly efficient AVERAGEIF function.
Understanding the Syntax
The AVERAGEIF function scans a range of cells, looks for a specific criteria (a word, a number, or a condition), and then calculates the average of only the rows that match.
Syntax: =AVERAGEIF(range, criteria, [average_range])
- range: The group of cells you want Excel to scan to see if they meet your condition (e.g., the column containing the school names).
- criteria: The specific condition you are looking for (e.g., “North High School” or “>50”). Must be in quotes if it is text or a mathematical operator.
- average_range: (Optional) The actual numbers you want to average (e.g., the column containing the test scores). If your
rangealready contains the numbers, you don’t need this.
Example 1: Averaging Based on Text
Imagine you have a sales report. Column A lists the Region (East, West, North, South) and Column B lists the Total Sales in dollars. You want to know the average sale size exclusively for the “East” region.
- Click an empty cell where you want the answer to appear.
- Type the formula:
=AVERAGEIF(A2:A100, "East", B2:B100) - Press Enter.
What Excel did: It scanned cells A2 through A100. Every time it saw the exact word “East”, it jumped over to the corresponding row in Column B and added that dollar amount to its hidden calculator. Finally, it divided the total by the number of matches to give you the perfect average.
Example 2: Averaging Based on Numbers (No Average Range)
You can also use AVERAGEIF to filter out outliers or ignore bad data within a single column.
Imagine you have a list of daily website visitors in Column A (cells A2:A30). A few days the website was down for maintenance, recording 0 visitors. If you use a normal AVERAGE function, those 0s will drag your average down, making it look like your site is underperforming.
You want to average the visitor count, but only for days where you had more than 0 visitors.
- Type the formula:
=AVERAGEIF(A2:A30, ">0") - Press Enter.
Notice we omitted the third argument. Because we are evaluating and averaging the exact same column, Excel doesn’t need to be told twice. It simply ignores the 0s and averages the real traffic days.