Yes, you can average values that meet a condition across multiple Excel sheets, but a single ordinary AVERAGEIF formula cannot take a 3-D reference spanning several sheets. The dependable method is to add each sheet’s matching-value total and divide by the total number of matching numeric values, or to combine the data into one range and use AVERAGEIF there.
Why AVERAGEIF Does Not Directly Span Sheets
AVERAGEIF evaluates a criteria range and an average range supplied to it. Its standard syntax is AVERAGEIF(range, criteria, [average_range]). The ranges must correspond to one another: each row or cell in the criteria range is paired with a value in the average range. Excel does not let you replace those arguments with a 3-D reference such as a range running from the first sheet to the third sheet.
This is different from simply averaging values in corresponding cells across worksheets. Excel supports 3-D references for some functions, but that does not mean every function accepts them. In particular, a construction such as AVERAGEIF(Jan:Mar!A2:A100,...) is not a valid way to apply criteria across those sheets. Instead, calculate the qualifying totals and counts separately for each sheet, then combine them.
This approach works in Excel for Microsoft 365 on Windows and in many earlier Excel versions that include AVERAGEIF, SUMIF, and COUNTIF. The formula examples below use commas as argument separators, as in a typical English-language Excel installation. Excel installations with different regional settings may display a different argument separator.
Use SUMIF and COUNTIF to Combine the Results
Suppose three worksheets named Jan, Feb, and Mar each have category names in A2:A100 and the corresponding amounts in B2:B100. Cell E2 on a summary sheet contains the category to look for, such as East. Assume every matching row has a numeric amount. With East amounts of 80 and 90 in Jan, 70 in Feb, and 100 in Mar, the combined average is 85.
With category names in A2:A100 and numeric amounts in B2:B100 on each of Jan, Feb, and Mar, and the requested category in E2 on the summary sheet, use this formula:
=(SUMIF(Jan!$A$2:$A$100,$E$2,Jan!$B$2:$B$100)+SUMIF(Feb!$A$2:$A$100,$E$2,Feb!$B$2:$B$100)+SUMIF(Mar!$A$2:$A$100,$E$2,Mar!$B$2:$B$100))/(COUNTIF(Jan!$A$2:$A$100,$E$2)+COUNTIF(Feb!$A$2:$A$100,$E$2)+COUNTIF(Mar!$A$2:$A$100,$E$2))
For the example, the numerator is 80 + 90 + 70 + 100, or 340. The denominator is four matching rows, so 340 divided by 4 returns 85. The dollar signs keep the referenced ranges and criterion cell fixed if you copy the formula. To add another worksheet, add its SUMIF to the numerator and its COUNTIF to the denominator.
In Microsoft 365 for Windows, enter the formula in a summary cell and press Enter. On Mac, the formula is the same; Excel’s ribbon and keyboard conventions differ in some places, but no special Mac-only version of this calculation is needed. If a sheet name contains spaces, surround it with single quotation marks in the reference, for example 'January Sales'!$A$2:$A$100.
Make the Formula Safe When Matching Rows Have Blanks
The simple formula assumes that every row whose category matches has a numeric value in the average column. That condition matters: COUNTIF counts matching categories even when the corresponding amount is blank or text, while an average should count only numeric values. If some matching rows have blank or text amounts, the simple formula can produce an average that is too low.
One robust option is to count numeric values paired with the selected category using SUMPRODUCT and ISNUMBER. With the same layout—categories in A2:A100, amounts in B2:B100 on each sheet, and the selected category in E2—use:
=(SUMIF(Jan!$A$2:$A$100,$E$2,Jan!$B$2:$B$100)+SUMIF(Feb!$A$2:$A$100,$E$2,Feb!$B$2:$B$100)+SUMIF(Mar!$A$2:$A$100,$E$2,Mar!$B$2:$B$100))/(SUMPRODUCT(--(Jan!$A$2:$A$100=$E$2),--ISNUMBER(Jan!$B$2:$B$100))+SUMPRODUCT(--(Feb!$A$2:$A$100=$E$2),--ISNUMBER(Feb!$B$2:$B$100))+SUMPRODUCT(--(Mar!$A$2:$A$100=$E$2),--ISNUMBER(Mar!$B$2:$B$100)))
Here each sheet’s numerator still sums numeric amounts matching the category, while its denominator counts only rows where the category matches and the amount is a number. If no matching numeric values exist, the division returns a divide-by-zero error. You can handle that separately if appropriate—for example, by checking the denominator first or using IFERROR to display a message. Decide what an empty result should mean before hiding the error; zero and “no matching data” are not the same result.
Use Multiple Criteria When Needed
If each row has more than one condition, use the plural functions SUMIFS and COUNTIFS. For example, you might need the average amount for the East region in a particular month. These functions accept additional range-and-criterion pairs, and you can still add the sheet-level totals and counts before dividing.
With region in A2:A100, month label in C2:C100, amount in B2:B100 on each sheet, and the desired region and month in E2 and F2, respectively, this formula combines Jan and Feb:
=(SUMIFS(Jan!$B$2:$B$100,Jan!$A$2:$A$100,$E$2,Jan!$C$2:$C$100,$F$2)+SUMIFS(Feb!$B$2:$B$100,Feb!$A$2:$A$100,$E$2,Feb!$C$2:$C$100,$F$2))/(COUNTIFS(Jan!$A$2:$A$100,$E$2,Jan!$C$2:$C$100,$F$2)+COUNTIFS(Feb!$A$2:$A$100,$E$2,Feb!$C$2:$C$100,$F$2))
This version also assumes that all rows meeting both conditions have numeric amounts. If blanks or text can occur in the amount column, use a numeric-only count as described above, adding both condition tests to each SUMPRODUCT count. Keep each criteria range the same size as its corresponding sum range; mismatched range sizes can cause errors or misleading results.
Do Not Average Sheet Averages Without Weighting Them
A common mistake is to calculate an average on each worksheet and then take the average of those sheet averages. That gives each sheet equal weight, regardless of how many matching records it contains. It is correct only when the sheets have the same number of qualifying numeric values, or when equal sheet weighting is genuinely the intended measure.
For example, suppose Jan has two matching values averaging 85, while Feb has one matching value of 70. Averaging the two sheet averages gives 77.5, but the combined-record average is (85 × 2 + 70 × 1) divided by 3, or 80. The sum-and-count formula calculates the combined-record average directly. If you already have sheet averages and counts, weight each average by its count, add those products, and divide by the sum of the counts.
Combine the Data for a Simpler Long-Term Setup
If the worksheets have the same column layout and are added regularly, a single consolidated table is often easier to maintain than a formula that grows with every new sheet. Put all records in one Excel table, with fields such as Date, Region, and Amount. Then a standard AVERAGEIF can operate over the consolidated columns. A table also expands as rows are added, reducing the need to update fixed ranges such as A2:A100.
Microsoft 365 and Excel 2024 also include VSTACK, which can stack corresponding ranges from several sheets into arrays. With categories in A2:A100 and values in B2:B100 on Jan, Feb, and Mar, and the selected category in E2, this is an option:
=AVERAGEIF(VSTACK(Jan!A2:A100,Feb!A2:A100,Mar!A2:A100),$E$2,VSTACK(Jan!B2:B100,Feb!B2:B100,Mar!B2:B100))
The stacked category and value ranges must have matching dimensions and be listed in the same sheet order. This method is convenient when the ranges are manageable, but it still names each sheet explicitly; it does not automatically discover every worksheet in a workbook. For larger recurring imports, Power Query can append tables into a consolidated dataset, after which ordinary worksheet formulas or a PivotTable can summarize it. Choose a method based on how often sheets change and how much data must be processed.
Common Problems and Checks
- A new sheet is missing: A formula that names Jan, Feb, and Mar will not include Apr automatically. Add its total and count, or use a consolidated source.
- Ranges do not align: The criteria range and average or sum range should cover corresponding rows. Check that each sheet uses the same row boundaries and column meanings.
- Text does not match exactly: Extra spaces or inconsistent labels such as East and EAST can make data difficult to audit. Clean or standardize category entries, and check the criterion cell for accidental spaces.
- The result is an error: A zero denominator usually means no qualifying numeric records were counted. Verify the criterion and inspect whether matching rows actually contain numbers.
- Numbers are stored as text: Values that look numeric but are stored as text may not be included as expected. Convert them to numbers before relying on the average.
- The formula is hard to maintain: Repeated sheet references are easy to mistype. Use consistent worksheet layouts, clear sheet names, and a visible summary area, or consolidate the records.
For a quick check, calculate the matching total and count for each sheet separately and compare their combined values with the final result. This makes it easier to spot a missing worksheet, a misaligned range, or a count that includes blank amounts.
Frequently Asked Questions
Can I use AVERAGEIF with a 3-D reference?
No. AVERAGEIF does not accept a multi-sheet 3-D range in its criteria argument. Add per-sheet matching totals and counts, use VSTACK in a supported Excel version, or consolidate the data.
Can I average the AVERAGEIF result from each sheet?
You can, but an ordinary average of sheet averages gives each sheet equal weight. For the average of all matching records, weight each sheet’s average by its numeric match count—or combine the totals and counts directly.
How do I include another worksheet?
Add that sheet’s SUMIF result to the numerator and its matching count to the denominator. Make sure the category and value ranges use the same layout as the existing sheets.
What if no sheet has a matching value?
The combined count is zero, so division produces an error. Check that the criterion matches the data and decide whether the summary should show a blank, a message, or another result when there are no matching numbers.
Does the VSTACK method update when I add a sheet?
No. You must include the new sheet in the VSTACK arguments. For a source that grows frequently, a consolidated table or an append process such as Power Query is generally easier to maintain.
Choosing the Right Method
For a few stable worksheets, summing the SUMIF results and dividing by the matching counts is transparent and compatible with a wide range of Excel versions. Use a numeric-only count if matching rows can contain blanks or text. For several criteria, use the corresponding SUMIFS and COUNTIFS pattern. If sheets are added often, consolidate the records or use VSTACK where available. The key is to average the underlying qualifying values—not the worksheet averages unless equal weighting is intentional.
Related
- Excel Data Analysis: How to Build a Useful Model
- Remove a Data Model from Excel: Steps and Effects
- Excel Data Modeling: How It Works and When 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 Date and Time: Formatting, Formulas, and FixesExcel stores dates as serial numbers and times as fractions of a day, so you can format them…
- Excel CONCAT Function Examples: Formulas and Practical TipsExcel’s CONCAT function joins text from cells and typed text into one result, such as a full name,…
- Excel LEFT Function: Extract Text from the Left SideTo extract characters from the beginning of a cell in Excel, use the LEFT function. If A2 contains…
- EDATE in Excel: Add or Subtract Months from a DateIn Excel, EDATE returns a date that is a specified number of months before or after a starting…
- Excel Data Validation List Formula: Examples and FixesFor an Excel drop-down list, select the cells, choose Data > Data Validation, set Allow to List, and…
- LEFT, RIGHT and MID Formulas in Excel: How to Use ThemUse LEFT to return characters from the start of a text value, RIGHT to return them from the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.