If you want to average only the numbers that meet a certain rule, the AVERAGEIF function in Google Sheets does it in one step. You tell it where to check, what to look for, and which numbers to average, and it skips everything that doesn’t match.
In this article, I’ll show you how to use AVERAGEIF with text labels, number thresholds, separate columns, wildcards, and a not-equal condition.
AVERAGEIF Function Syntax in Google Sheets
The AVERAGEIF function averages the values in a range that meet a single condition.
=AVERAGEIF(criteria_range, criterion, [average_range])
- criteria_range is the range you test against the condition.
- criterion is the rule a cell must meet, like “East”, “>100”, or “A*”.
- average_range is the range of numbers to average. Leave it out and AVERAGEIF averages the criteria range itself.
When to Use AVERAGEIF Function
- Average values that match a text label, like sales for one region.
- Average only the numbers above or below a threshold.
- Test one column and average a different one when your data spans several columns.
- Use a wildcard to average rows whose text starts with or contains something.
- Average everything except one category with a not-equal condition.
Example 1: Average Values That Match a Text Label
Let’s start with the most common job, averaging the rows that match one label.
Below is the dataset. Column A has the region and column B has the sales for each row.
I want the average sales for the East region only.
Here is the formula:
=AVERAGEIF(A2:A6, "East", B2:B6)
AVERAGEIF looks down column A for “East”, then averages only the matching sales in column B. Three rows are East, holding 200, 300, and 100, so the average comes out to 200. The West and North rows are ignored entirely.
This is the conditional sibling of the SUMIF function. Where SUMIF totals the matching rows, AVERAGEIF averages them.
Example 2: Average Numbers Above a Threshold
Sometimes the column you test is the same column you want to average.
Below is the dataset. Column A has the product and column B has the units sold.
I want the average of only the units values above 100.
Here is the formula:
=AVERAGEIF(B2:B6, ">100")
Notice there’s no third argument here. When the range you test is also the range you average, you can leave out the average range and AVERAGEIF uses the criteria range for both.
Two values clear 100, which are 120 and 200, so the average is 160. The smaller values are left out.
Pro Tip: The comparison operator and the number go together inside one set of double quotes, like “>100”. Splitting them or dropping the quotes will break the formula.
Example 3: Test One Column, Average a Different One
The range you test and the range you average don’t have to touch.
Below is the dataset. Column A has the salesperson, column B has the region, and column C has the deal amount.
I want the average amount for deals in the East region.
Here is the formula:
=AVERAGEIF(B2:B5, "East", C2:C5)
Here the criteria range is column B and the average range is column C, two separate columns. AVERAGEIF finds the East rows in B, then averages the matching amounts in C.
Three rows are East, holding 500, 400, and 300, so the result is 400. Just make sure both ranges cover the same rows, or the matching gets thrown off.
Example 4: Average Using a Wildcard Text Match
You don’t have to match a label exactly. A wildcard lets you match a pattern.
Below is the dataset. Column A has the customer name and column B has their spend.
I want the average spend for every customer whose name starts with the letter A.
Here is the formula:
=AVERAGEIF(A2:A5, "A*", B2:B5)
The asterisk in “A*” stands for any characters that follow, so it matches any name beginning with A. Apple Inc, Adobe, and Amazon all qualify, with spends of 1000, 800, and 900, so the average is 900. Microsoft doesn’t start with A, so it’s skipped.
Use a question mark instead of an asterisk when you want to match exactly one character rather than any number of them.
Example 5: Average Everything Except One Category
You can also flip the logic and average everything that does not match.
Below is the dataset. Column A has the expense category and column B has the cost.
I want the average cost of every row that is not in the Food category.
Here is the formula:
=AVERAGEIF(A2:A6, "<>Food", B2:B6)
The “<>” operator means not equal to, so “<>Food” matches every category except Food. The non-Food rows hold 120, 90, and 90, so the average is 100. Both Food rows are left out.
This is a clean way to exclude one group without having to list all the categories you do want.
Tips & Common Mistakes
- Wrap operators and text criteria in double quotes. Conditions like “>100”, “<>Food”, and “A*” must sit inside quotes, or the formula won’t parse. A plain text match like “East” needs them too.
- Keep the criteria range and average range the same size. If they cover different numbers of rows, the matching shifts and you get a wrong average or an error. Line them up row for row.
- For more than one condition, switch to AVERAGEIFS. AVERAGEIF handles a single rule. When you need to average on two or more conditions at once, use AVERAGEIFS, the same way you’d move from SUMIF to the SUMIFS function.
AVERAGEIF is the go-to for averaging just the rows that match a rule.
You’ve now seen it with text labels, number thresholds, separate columns, wildcards, and a not-equal condition. Pick the criterion that fits your data and AVERAGEIF handles the rest.
List of All Google Sheets Functions
Related Google Sheets Functions / Articles: