Yes. In Excel, AVERAGEIF can use a text criterion—such as a department name or status—to select which rows to average. It averages numeric values from the matching rows; it does not calculate an average of text itself.
What AVERAGEIF Does with Text
AVERAGEIF checks one range for a condition, then averages the corresponding numeric cells in an average range. Text is useful as the condition: for example, you can ask Excel to average sales for rows labeled “East.” The text label determines which records qualify, while the sales amounts supply the numbers for the calculation.
Suppose A2:A6 contains East, West, East, East, and West, while B2:B6 contains 120, 80, 150, 90, and 110. With regions in A2:A6 and sales amounts in B2:B6, this formula averages the values where the region is East:
=AVERAGEIF(A2:A6,"East",B2:B6)
The result is 120: Excel adds 120, 150, and 90 to get 360, then divides by the three matching numeric values. The words in column A are not part of the arithmetic. This distinction answers a common misconception: AVERAGEIF can average by text, but it cannot produce a meaningful arithmetic average of words.
Syntax and the Three Arguments
The function has this structure: AVERAGEIF(range, criteria, [average_range]). The square brackets indicate that the third argument is optional; do not type the brackets into the formula. Each argument has a specific job:
- range is the cells Excel checks against the condition, often a column of names, categories, or statuses.
- criteria is the condition, such as a quoted text label or a reference to a cell containing that label.
- average_range contains the numbers to average for qualifying rows. If omitted, Excel averages numeric cells in
rangeitself.
For a text-based average, you will usually provide all three arguments: one range for labels, a text criterion, and another range for numeric data. It is clearest and safest to make the criteria range and average range the same size and aligned row by row. If your labels are in A2:A100 and the corresponding values are in C2:C100, use those matching spans rather than including a header in one range or starting the ranges on different rows.
If you omit average_range and the criteria range contains words, Excel has no numeric values there to average. That is not the same as asking Excel to average the amounts in a neighboring column; specify the amount range explicitly when the numbers are stored elsewhere.
Exact Text Matches and Cell References
For a fixed label, put the text in quotation marks inside the formula. Text matching with AVERAGEIF is not case-sensitive, so “East,” “EAST,” and “east” match the same text for this purpose. Extra spaces can still matter: a cell containing “East ” with a trailing space may not behave like a clean “East” label, so trim or standardize inconsistent source data if results look unexpectedly low.
Using a cell reference instead of typing the criterion makes a worksheet easier to reuse. With region names in A2:A6, sales in B2:B6, and the requested region in D2 (where D2 contains East), use:
=AVERAGEIF(A2:A6,D2,B2:B6)
The result is 120 for the sample values above. Change D2 to West and Excel returns 95, the average of 80 and 110. A dropdown in the criterion cell can make this kind of summary easier for other people to use, while the formula stays unchanged.
Wildcards for Partial Text
Text criteria can include wildcards when labels are not identical or you want to match a phrase within a longer label. The asterisk (*) matches any number of characters, including none; the question mark (?) matches one character. Wildcards are useful for category names such as “North Retail” and “South Retail,” or labels with a shared prefix.
Suppose A2:A4 contains North Retail, South Retail, and North Wholesale, and B2:B4 contains 100, 140, and 80. With those labels in A2:A4 and amounts in B2:B4, this formula matches text ending in Retail:
=AVERAGEIF(A2:A4,"*Retail",B2:B4)
The result is 120, the average of 100 and 140. The wildcard at the beginning allows any characters before “Retail.” By contrast, a criterion such as "North*" matches labels that begin with North. A criterion of "*Retail*" can match Retail anywhere in the cell.
If the text you need to match literally contains an asterisk or question mark, place a tilde before that character so Excel treats it as a character rather than a wildcard. For instance, a tilde can escape a literal asterisk in a criterion. Wildcards do not turn AVERAGEIF into a case-sensitive search; use a different approach if distinguishing uppercase from lowercase is essential.
When the Values to Average Are Text
AVERAGEIF averages numeric cells, not text that merely looks like a number. For example, a cell containing the number 25 formatted to display as text is different from a cell containing the text string “25.” If the matching cells in the average range contain only text or numeric text, Excel has no numeric values to average; when there are no qualifying numeric values, the result is typically #DIV/0!.
Check how the source values are stored before changing the formula. A number aligned or formatted unexpectedly may still be numeric, and formatting a cell as Text does not by itself convert an existing number into text. One quick check is to select a value and inspect the formula bar or test a representative cell with Excel’s ISNUMBER function. If the data came from a CSV export or another system, imported numeric text is a common cause of an empty average.
One option is to convert the source column to real numbers, then keep the original AVERAGEIF formula. If conversion is not appropriate for the source data, a helper column can convert each usable text number. With labels in A2:A4, numeric text in C2:C4, and a helper formula entered in D2 and filled down, use:
=IFERROR(VALUE(C2),"")
VALUE converts text that Excel recognizes as a number; IFERROR leaves an empty string for entries it cannot convert. Then, with labels in A2:A4 and converted values in D2:D4, average the matching rows with:
=AVERAGEIF(A2:A4,"East",D2:D4)
For example, if the East rows contain text values 10 and 20, the result is 15. Check that Excel recognizes the text’s decimal and thousands separators in your regional settings. Do not convert values blindly if the cells contain units, currency symbols, or other content that needs separate cleanup.
More Than One Condition
AVERAGEIF accepts one criterion range and one condition. When the average must satisfy multiple conditions—for example, a department and a transaction type—use AVERAGEIFS, which is designed for multiple criteria. It starts with the average range, followed by pairs of criteria ranges and criteria.
Suppose A2:A6 contains departments, B2:B6 contains transaction types, and C2:C6 contains amounts. The rows are East/Retail/100, East/Wholesale/160, West/Retail/80, East/Retail/140, and West/Wholesale/120. To average amounts where the department is East and the type is Retail, use:
=AVERAGEIFS(C2:C6,A2:A6,"East",B2:B6,"Retail")
The result is 120, because the two rows meeting both conditions have amounts of 100 and 140. A row must satisfy both criteria to be included. As with AVERAGEIF, align the ranges so the cells on each row refer to the same record. If you only need one condition, AVERAGEIF is simpler; switching to AVERAGEIFS is useful when the logic genuinely has multiple tests.
Common Errors and How to Check Them
An incorrect result is often caused by the data or range selection rather than the function syntax. Work through these checks before rebuilding the formula:
- Confirm the criterion range. It should contain the labels you intend to test, not the values you intend to average.
- Check the average range. Make sure it contains the corresponding numeric data and starts on the same record row as the criteria range.
- Look for inconsistent text. Leading or trailing spaces, spelling differences, and alternate labels can prevent rows from matching the intended category.
- Check for numeric text. If qualifying values are stored as text, convert them or use a carefully checked helper column.
- Interpret zero and blanks correctly. Blank cells in the average range do not contribute a number. A genuine numeric zero does contribute and can lower the average.
- Investigate
#DIV/0!. It can indicate that no rows met the criterion or that the matching average-range cells contained no numeric values.
Also verify that a criterion stored in a cell does not accidentally include spaces, and that your ranges include the intended rows but not headings. If the criteria and average ranges have different dimensions, revise them to matching, aligned ranges rather than relying on Excel’s handling of mismatched spans. Clear range design makes both the formula and its result easier to audit.
Frequently Asked Questions
Can AVERAGEIF use text as its condition?
Yes. Put a text criterion in quotation marks, such as "East", or refer to a cell containing the text. Excel uses the text to decide which rows qualify, then averages numeric values in the corresponding average-range cells.
Can AVERAGEIF average words or text values?
No. AVERAGEIF calculates an arithmetic mean from numeric values. Text in the criteria range can select records, but text in the average range is not treated as a number. Convert valid numeric text to numbers before averaging.
Does AVERAGEIF distinguish uppercase and lowercase text?
No. Its text matching is case-insensitive, so capitalization alone does not separate labels. If your task requires case-sensitive matching, AVERAGEIF by itself is not the right tool.
Why does my text-based AVERAGEIF return #DIV/0!?
Usually, there are no matching rows, or the matching cells in the average range contain no numeric values. Check the spelling and spaces in the criterion, the selected ranges, and whether number-like entries are actually stored as numbers.
Can I use a wildcard with AVERAGEIF?
Yes. Use * for any number of characters and ? for one character in a text criterion. For example, "*Retail" matches text ending in Retail. Use a tilde to match a wildcard character literally.
Related
- Excel Timesheet for Multiple Employees: Setup and Tips
- Excel Invoice Bill Format: Build a Useful Template
- Excel Accounting Solver: What It Does and How to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sort Dates From Earliest to Latest: A Clear GuideTo sort dates in Excel from earliest to latest, select a cell in your date column, then choose…
- Remove Duplicates in Excel: Sort Data the Right WayTo remove duplicates in Excel and keep the right record, sort your data first if the order determines…
- Excel Cell Value From List: Find or Select a MatchTo return a value from a list when you know a matching item, use XLOOKUP; with product IDs…
- IRR Function in Excel: Calculate an Investment ReturnUse Excel’s IRR function to estimate the rate of return for a series of cash flows that occur…
- COUNTIFS in Excel: Exact and Partial Text MatchesYes. In Excel, COUNTIFS can count cells that match text exactly or contain a text fragment: use =COUNTIFS(A2:A100,"Apple")…
- Excel Data Validation INDIRECT for Dependent ListsYes. In Excel, INDIRECT can make a data-validation drop-down depend on another cell: name each child list after…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.