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
- Correlation Table in Excel: Create and Read One
- Python Pandas Excel Export: Methods and Examples
- CSV File to Excel Converter: Convert Without Data Loss
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calculate Age in Weeks in Excel with a Simple FormulaTo calculate completed age in weeks in Excel, put a birth date in A2 and enter =INT((TODAY()-A2)/7) in…
- COUNTA and COUNTIF in Excel: How to Use Them TogetherTo find what share of filled status cells say Complete, with statuses in B2:B11, use =COUNTIF(B2:B11,"Complete")/COUNTA(B2:B11). If five…
- Identify Offsetting Entries in Excel With Formulas and ChecksTo flag entries with an equal and opposite amount, put the date in column A, account in B,…
- Excel Link to Named Range: Formulas and MethodsTo create a clickable link to a named range in Excel for Microsoft 365 on Windows, use the…
- XLOOKUP Formula: Real Examples in ExcelThe basic XLOOKUP formula is =XLOOKUP(E2,A2:A6,B2:B6): it looks for the value in E2 within A2:A6 and returns the…
- Sort Excel Workbook Tabs Alphabetically in ExcelExcel does not have a built-in command that automatically sorts workbook tabs alphabetically. In Microsoft Excel for Microsoft…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.