To count distinct nonblank values across worksheets in Excel for Microsoft 365, stack the ranges and count the unique results. With entries in A2:A100 on sheets named Jan, Feb, and Mar, use =LET(all,VSTACK(Jan!A2:A100,Feb!A2:A100,Mar!A2:A100),IF(SUM(--(all<>""))=0,0,ROWS(UNIQUE(FILTER(all,all<>""))))). It returns one total for the combined ranges, so a value repeated on different sheets is counted once.
What the formula counts
Suppose Jan contains Red, Blue, and Green; Feb contains Blue and Yellow; and Mar contains Green and a blank. The combined list has four distinct nonblank values: Red, Blue, Green, and Yellow. The formula returns 4, not 6, because the second Blue and second Green are repeats.
This is different from adding the unique count on each sheet. Jan has three unique values and Feb has two, but adding those sheet totals gives five and counts Blue twice. A cross-sheet distinct count treats all the selected cells as one collection before removing duplicates.
The formula excludes genuinely empty cells and cells whose formulas return an empty string. It counts values in the specified ranges only; entries below row 100, or in other columns, are outside the calculation. Adjust the references to match your data.
How the formula works
The formula uses four functions available in Microsoft 365: LET, VSTACK, FILTER, and UNIQUE. VSTACK places the three ranges into one vertical array. LET gives that combined array the short name all, making the rest of the formula easier to read.
FILTER(all,all<>"")removes blank entries before counting. This matters because UNIQUE can return a blank as one of its distinct results.UNIQUE(...)returns one instance of each distinct value in the filtered array.ROWS(...)counts the resulting list. It is suitable here because UNIQUE returns a vertical array.SUM(--(all<>""))=0checks whether there are any nonblank entries. If there are none, the formula returns 0 instead of trying to filter an empty list.
In the formula, the range references use sheet names without spaces. If a sheet name contains a space, enclose it in single quotation marks: for example, use 'North Region'!A2:A100. The single quotes are part of the worksheet reference.
Set up the ranges carefully
Before entering the formula, check that each sheet contributes the same kind of information. If you are counting customer names, use the name column on every sheet; mixing names with order numbers would give a technically valid count but an unhelpful result. The ranges do not have to be the same length, although matching row limits are easier to inspect.
- Identify the value column and the first and last rows to include on each worksheet.
- Replace Jan, Feb, and Mar with your actual tab names, and update each range reference.
- Enter the formula in a summary cell outside the source ranges, such as a Summary sheet.
- Press Enter. If the result looks wrong, check that every relevant worksheet and row is included.
For example, if one sheet has data in D5:D250 and another in D5:D400, reference those actual ranges rather than copying the A2:A100 example unchanged. A formula in an Excel table or a defined name can make a larger workbook easier to maintain, but the basic principle remains: combine the intended cells, remove blanks, and count the unique results.
Blanks, errors, and changing data
Blank handling is a common source of off-by-one results. A cell that looks empty may contain a formula returning an empty string, such as a formula whose result is "". The test against an empty string excludes both that case and an ordinary blank. A cell containing one or more spaces is not blank to Excel, however, so it will be treated as a value. If spaces should not count, clean or trim the source data before counting.
Source errors such as #N/A can also affect the calculation. The formula deliberately does not hide such errors: an error in the stacked array can flow through the comparison or the later functions. Find and correct the source error, or decide explicitly how errors should be handled before changing the formula. Wrapping the whole calculation in IFERROR may make the display look tidy, but it can also conceal a real problem by turning an error into a misleading number.
Fixed ranges are straightforward, but they will not automatically include entries added below their last row. You can extend the references as the lists grow, use ranges sized for expected additions, or organize the source data as Excel tables and build a formula around the table columns. If you add another worksheet to the workbook, the formula will not include it until you add that sheet’s range to VSTACK.
What Excel considers the same value
UNIQUE compares values without treating uppercase and lowercase letters as separate categories. Thus, Red and RED are normally one distinct text value for this purpose. Leading or trailing spaces, by contrast, can make text entries different: Red and Red may count separately. Inconsistent spelling, punctuation, and hidden characters can also produce a higher count than expected.
Numbers and text that look like numbers can be different underlying values. A numeric 123 may not be equivalent to text stored as 123, even though the cells appear similar. If your count seems unexpectedly high, inspect the data types and use consistent formatting or convert the source values to a consistent type. Formatting alone does not necessarily change a cell’s underlying value.
There is also a practical distinction between counting distinct items and counting distinct combinations. The example formula counts one column. If a person should be unique only when both their name and department match, that is a two-column distinct-record problem; counting names alone would merge people who share a name. Decide what defines a duplicate before choosing the ranges and method.
Compatibility and alternatives
The formula above is aimed at Microsoft 365 for Windows. VSTACK is available in Microsoft 365 and Excel 2024; UNIQUE and FILTER are available in Microsoft 365 and Excel 2021 or later. Because the example uses VSTACK, it will not work in older Excel versions that lack that function. Excel may show #NAME? when it does not recognize a function.
For an older version, a practical approach is to copy the relevant columns from each sheet into one temporary column, then use Excel’s Advanced Filter to extract unique records to another location and count the resulting list. In Excel for Windows, the command is on Data > Advanced in the Sort & Filter group; select Copy to another location and Unique records only. Work on a copy or temporary area so you do not alter the source lists. The extracted result is a snapshot, so repeat the process after the source data changes.
Power Query is another option for recurring reports or larger data sets. It can combine (append) tables from multiple sheets and remove duplicates in the combined result. The steps depend on how the workbook is organized and the Excel version, but a useful workflow is to make each source range a table, append the tables, and remove duplicates from the relevant column. Refresh the query when source data changes. Unlike a live formula, a query result generally needs refreshing to reflect new entries.
In Excel for the web, function availability can depend on the account and current release, so check whether the formula is recognized in the workbook. Excel for Mac has its own ribbon layout and shortcut differences; the formula itself is generally the same when the required functions are available. If a workbook is shared across different Excel versions, a Power Query output or a consolidated helper list may be more compatible than a formula relying on newer functions.
Common problems and checks
- The result is too high: look for extra spaces, spelling variations, hidden characters, or values that are stored as text in some sheets and numbers in others.
- The result is too low: confirm that the formula includes every worksheet and the full used range. Also verify that the selected column contains the identifier you intended to count.
- You see #NAME?: check for a misspelled function or a version that does not support VSTACK, UNIQUE, or FILTER.
- You see an error from a source cell: inspect the referenced ranges for error values rather than suppressing the error without understanding it.
- The total does not change after adding rows: check whether the new rows fall within the formula’s fixed range, and whether you included the new worksheet in the stacked ranges.
For a quick audit, place the same ranges in a temporary stacked list and inspect the UNIQUE output. In Microsoft 365, you can enter =UNIQUE(VSTACK(Jan!A2:A100,Feb!A2:A100,Mar!A2:A100)) in an unused cell to see the distinct list, including a possible blank. This diagnostic formula assumes values are in A2:A100 on Jan, Feb, and Mar; it is not the blank-excluding count formula. Comparing the visible list with the source data often reveals why a total differs from expectations.
Frequently Asked Questions
Can I count unique values across sheets with different range sizes? Yes. VSTACK can combine ranges with different row counts. Update each sheet reference to cover its actual data, and make sure the selected columns represent the same kind of value.
Does the formula count blanks? The main formula excludes ordinary blank cells and formulas returning an empty string. Cells containing spaces are not empty, so trim or clean them if they should be ignored.
Can I include more than three worksheets? Yes. Add each additional range as another argument to VSTACK, separated by a comma. For a large number of changing sheets, maintaining a formula with every tab listed may be cumbersome; Power Query can be easier to refresh and manage.
Why does Excel return #NAME? for VSTACK? Your Excel version may not include VSTACK, or the function name may be misspelled. VSTACK is available in Microsoft 365 and Excel 2024. For older versions, consolidate the values and use Advanced Filter or another compatible workflow.
Can I count unique values across every sheet automatically? The displayed formula names its sheets explicitly, so a newly created tab is not included automatically. For a workbook with a changing set of sheets, use a maintained consolidation process such as Power Query rather than assuming a formula will discover new tabs.
Related
- How to Extend Conditional Formatting in Excel
- How to Highlight Duplicates in Excel Automatically
- Excel Conditional Formatting Question Mark Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort in Excel and Move Entire Rows Without Splitting DataTo sort in Excel and move entire rows together, select the full data range, then choose Data >…
- Excel MAX Function: Find the Highest Value in a RangeTo find the largest number in Excel, enter a formula such as =MAX(B2:B10) in an empty cell, replacing…
- Excel: How to Hide Columns to the Right of Your DataTo hide every column to the right of your data in Excel for Microsoft 365 on Windows, select…
- COUNTIF Excel: How to Count Matches and Sum ValuesCOUNTIF in Excel counts cells that meet one condition; it does not add the values in those cells.…
- Excel Formula: Capitalize Only the First Letter in TextTo capitalize the first character in a cell and make the remaining characters lowercase, use =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))), assuming the…
- Remove Spaces After Text in Excel Cells with TRIMTo remove only ordinary spaces at the end of text while preserving spaces inside words, put this formula…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.