AVERAGEIF Across Sheets in Excel: Formulas & Examples

Formulas & functions · Updated November 18, 2026

Excel’s AVERAGEIF function cannot directly average matching values across multiple worksheets in one formula. Assuming Jan and Feb each have names in A2:A100, values in B2:B100, and the criterion in E2, add the matching totals and divide by the count of matching numeric values with this formula: =(SUMIF(Jan!$A$2:$A$100,$E2,Jan!$B$2:$B$100)+SUMIF(Feb!$A$2:$A$100,$E2,Feb!$B$2:$B$100))/(SUMPRODUCT((Jan!$A$2:$A$100=$E2)*ISNUMBER(Jan!$B$2:$B$100))+SUMPRODUCT((Feb!$A$2:$A$100=$E2)*ISNUMBER(Feb!$B$2:$B$100))). This produces one correctly weighted average across the two sheets, rather than averaging their averages.

Why AVERAGEIF needs a workaround across sheets

AVERAGEIF evaluates a criterion against a range and averages the corresponding cells in an average range. Its syntax is AVERAGEIF(range, criteria, [average_range]). The ranges in a normal AVERAGEIF call belong to one worksheet; Excel does not let you provide a list of separate sheet ranges or a three-dimensional reference such as Jan:Feb!A2:A100 as the function’s criteria range.

For a single worksheet, with names in A2:A100, values in B2:B100, and the value to match in E2, the ordinary formula is =AVERAGEIF(A2:A100,E2,B2:B100). If you leave out the optional average_range, Excel averages the cells in the criteria range itself. Across sheets, the key is to combine the underlying totals and counts, or to bring the records together first.

That distinction matters because the monthly sheets may have different numbers of matching records. A single average of monthly averages gives every month equal weight, even when one month has many more records than another. Combining sums and counts gives each matching record equal weight, which is the usual meaning of an overall average.

How the multi-sheet formula works

The example formula has two parts for each worksheet. SUMIF adds the values in column B where the corresponding name in column A matches E2. SUMPRODUCT with ISNUMBER counts only those matching rows whose value cells contain numbers. The formula adds the sums from both tabs, then divides by the combined numeric count.

Counting numeric values explicitly is useful because AVERAGEIF ignores blank cells and text in its average range. A simple COUNTIF of the names would count matching rows even when the corresponding value cell is blank, which could make the answer too low. The ISNUMBER test avoids counting those blanks or text entries. It also makes the formula more faithful to what readers generally expect from an average of numeric data.

Use the same row limits for each criteria range and its corresponding value range. In the example, Jan!A2:A100 is paired with Jan!B2:B100, and Feb!A2:A100 with Feb!B2:B100. The ranges can be longer or shorter, but each pair must cover corresponding rows. If adding a new month, add another SUMIF term to the numerator and another matching numeric-count term to the denominator.

Worked example with two monthly sheets

Suppose the Jan tab contains Tea sales of 10 and 20, and the Feb tab contains one Tea sale of 30. Put Tea in E2, with product names in column A and sales amounts in column B on both tabs. The combined sum is 10 + 20 + 30, or 60; the matching numeric count is 3. The formula therefore returns 20.

If Jan has ten matching records and an average of 12, while Feb has one matching record with a value of 30, averaging the two monthly averages gives 21. But the record-level overall average is (10 × 12 + 1 × 30) ÷ 11, approximately 13.64. The sum-and-count method returns that weighted result. It is not adding an arbitrary statistical adjustment; it is simply averaging all the included observations together.

To set it up, enter the criterion in E2 on a summary sheet, paste the formula into the result cell, and press Enter. Excel for Microsoft 365 on Windows recalculates the formula when source values change. If you copy it down for other criteria, the dollar signs keep the source ranges fixed while the row reference in E2 changes to E3, E4, and so on.

Criteria, operators, and worksheet names

The criterion can be text, a number, a cell reference, or a comparison expression. For example, a criterion cell containing Tea matches that text; a criterion such as greater than 100 can be built by joining an operator to a cell reference. AVERAGEIF and SUMIF also support wildcard matching: an asterisk represents any sequence of characters, and a question mark represents one character. A tilde can be used to treat a wildcard character literally.

For a criterion typed directly into the formula, comparison operators and text criteria need quotation marks in Excel syntax. When the criterion is stored in a cell, reference that cell instead; this makes it easier to change the condition without editing the formula. Keep the criterion consistent across the separate SUMIF and count terms, or the numerator and denominator will describe different sets of rows.

Sheet names without spaces can be written as Jan!A2:A100. If a tab name contains spaces or punctuation, wrap it in single quotation marks, as in ‘January Sales’!A2:A100. If you rename a worksheet in Excel, Excel normally updates formulas that refer to it. For a formula involving many tabs, check each sheet reference after inserting, renaming, or deleting sheets.

Common problems and how to fix them

  • The result is #DIV/0!: No rows met the criterion with numeric values in the paired value ranges. Check spelling, extra spaces, the criterion cell, and whether amounts are stored as numbers rather than text.
  • The result is too low or too high: Confirm that each criteria range is paired with its own value range and that the ranges cover the same rows. Also check that you are not averaging monthly averages when you need a record-level result.
  • Excel reports a formula error: Look for a missing parenthesis, an incorrect tab name, or mismatched range sizes. A tab name with spaces needs single quotation marks around the name.
  • Some expected rows are not included: Text comparisons can be affected by leading or trailing spaces and inconsistent labels. Clean the source data, or use a consistent criterion and naming convention.
  • Values seem to be ignored: Numbers imported as text are not numeric values for the ISNUMBER count and may not contribute to the sum as intended. Convert those cells to real numbers before relying on the average.

If there are no qualifying numeric values, the formula has no denominator and returns a division error. You can decide whether a blank, a message, or an error is most useful for your report, but the correct choice depends on how downstream formulas and readers should interpret “no data.” Avoid hiding the error automatically if it could conceal a broken criterion or a data-import problem.

When to consolidate the data instead

A formula that names every worksheet is manageable for two or three stable tabs, but it becomes harder to maintain as the number of sheets grows. For recurring reports, consider combining the records into one table with columns such as Month, Product, and Sales. Then a normal AVERAGEIF can use the product column as its criteria range and the sales column as its average range; filtering by month can be handled separately if required.

Excel tables are helpful when new rows are added because their structured references expand with the table. For a workbook that receives a new file or worksheet each month, Power Query can append similarly structured data into one query result, which can then be loaded to a worksheet and summarized. The exact setup depends on how the source files are organized, but the general benefit is that the calculation refers to one consolidated dataset rather than a growing list of tabs.

Consolidation is not always necessary. If the workbook is small and each tab represents a fixed period, the explicit formula is transparent and easy to audit. If tabs have different layouts, inconsistent column positions, or changing names, consolidating and standardizing the data can prevent reference mistakes as well as simplify the average.

AVERAGEIF versus other approaches

AVERAGEIF is suitable when there is one condition, such as average sales for Tea. AVERAGEIFS is the related function for multiple conditions, such as a product and a region, but it does not remove the cross-sheet limitation: separate sheets still need to be combined or their sums and counts handled explicitly. For more complex conditions across several tabs, a consolidated table is often clearer than a long formula.

Another tempting shortcut is to take the arithmetic mean of each sheet’s AVERAGEIF result. That is correct only when every sheet contributes the same number of qualifying numeric values, or when equal weighting of sheets is specifically the intended statistic. Otherwise, it gives a different answer from averaging all matching records. A weighted average of sheet averages can work if you also have each sheet’s matching count, but adding sums and counts is usually easier to verify.

Frequently Asked Questions

Can AVERAGEIF use a 3D reference across worksheets?
No. AVERAGEIF does not accept a multi-sheet 3D reference as the range to evaluate. Use separate sheet calculations in a combined sum-and-count formula, or consolidate the data into one range.

Can I average the same cell range on several sheets?
Yes, but not by passing all the separate ranges to one AVERAGEIF call. If each tab uses the same layout, add the matching sums and counts from each tab, as in the formula above. Keep the criteria and value columns aligned across sheets.

Does the formula count blank cells?
No. The numeric-count portion uses ISNUMBER, so blanks and text values in the value ranges do not increase the denominator. If a value is stored as text even though it looks like a number, convert it to a numeric value if it should be included.

Can I use a different condition on each sheet?
You can, but each sheet’s SUMIF and count test must use the intended condition for that sheet. Make sure each numerator term and its corresponding denominator term use the same criterion; otherwise, the calculated average will not represent one consistent group.

What is the easiest option when I have many tabs?
Combine the records into one standardized table, manually for a small static workbook or with a repeatable import process such as Power Query for recurring data. A single dataset is easier to extend, inspect, and summarize than a formula that must name every worksheet.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.