To count distinct, nonblank values in Excel 365, use =COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>""))) when the values are in A2:A100. COUNTIF alone counts matching cells, including duplicates; UNIQUE removes repeated values before the result is counted.
What “COUNTIF without duplicates” can mean
The phrase can describe two different questions. The first is “How many different values are in this range?” For example, if a list contains Maya, Luis, Maya, Noor, Luis, there are three distinct names. The second is “How many values appear exactly once?” In that same list, only Noor appears once. Those are not the same calculation, so decide which result you need before choosing a formula.
COUNTIF is designed to count cells that meet a condition, not to create a distinct list. With names in A2:A6, for instance, =COUNTIF(A2:A6,"Maya") returns 2 because Maya occurs in two cells. It does not automatically count Maya only once. For a distinct count, remove duplicates from the values being counted, then count what remains.
Count all distinct values and ignore blanks
With values in A2:A100, use =COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>""))). FILTER first removes blank cells, UNIQUE returns one instance of each remaining value, and COUNTA counts that resulting list. For the sample list Maya, Luis, Maya, Noor, Luis, followed by blank cells, the result is 3.
UNIQUE and FILTER are available in Microsoft 365 and Excel 2021 or later. The formula returns a single count, so it does not spill a visible list onto the worksheet. If you want to inspect the distinct values as well as count them, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")) in an empty cell; the returned list spills into cells below. Leave that spill area clear or Excel may show a #SPILL! error.
Filtering blanks matters because a plain UNIQUE calculation can include an empty entry from the source range. COUNTA normally ignores genuinely empty cells, but the intermediate result may represent a blank as a value. Explicitly filtering out cells that equal an empty string makes the intended treatment clear, including when source cells contain formulas that return “”.
Count distinct values that meet a condition
Suppose column A contains a region and column B contains a product, with the region to check in E2. To count nonblank products sold in that region only, use =IFERROR(ROWS(UNIQUE(FILTER(B2:B100,(A2:A100=E2)*(B2:B100<>"")))),0). The comparison selects matching regions, the second condition excludes blank product cells, and the asterisk combines the two conditions as AND. UNIQUE removes repeated products; ROWS counts the remaining entries.
For example, if E2 contains North and the matching product entries are Pen, Pad, Pen, and Clip, the result is 3. If no North rows have a nonblank product, FILTER has no results and would normally return an error. IFERROR makes the formula return 0 in that case. Use the same row limits for both source columns so each region remains paired with its correct product.
You can add more conditions by multiplying additional true-or-false tests inside FILTER. For example, a date range or a status column can narrow the rows before UNIQUE runs. Keep the conditions in parentheses, and make sure every condition array covers the same rows as the values being deduplicated. This is more reliable than applying a condition to one range and accidentally counting values from another.
Count values that occur exactly once
If “without duplicates” means “count only entries whose frequency is one,” use a frequency check rather than a simple distinct count. With names in A2:A100, use =SUM(--(COUNTIF(A2:A100,UNIQUE(FILTER(A2:A100,A2:A100<>"")))=1)). FILTER excludes blanks, UNIQUE supplies one copy of each name, and COUNTIF calculates how often each distinct name occurs in the original range. The comparison keeps only names with a count of 1, and SUM adds those true results.
For a list containing Maya, Luis, Luis, and Noor, this formula returns 2: Maya and Noor each occur once. Luis is excluded because it occurs twice. By contrast, the ordinary distinct-count formula returns 3 because it counts Luis once regardless of how many times he appears. This distinction is useful in tasks such as finding one-off entries, but it is not usually the right measure for counting unique customers or products.
Use COUNTIF with a helper column in older Excel
In versions without UNIQUE and FILTER, a helper column can mark the first appearance of each value. With source values in A2:A100, enter =IF(A2="","",COUNTIF($A$2:A2,A2)=1) in B2 and fill it down. The expanding range starts at A2 and ends at the current row, so the first appearance receives TRUE and later repeats receive FALSE. Blank source cells stay blank.
Then, with those flags in B2:B100, use =COUNTIF(B2:B100,TRUE) to count the distinct nonblank values. For the names Maya, Luis, Maya, Noor, Luis, the helper marks the first Maya, first Luis, and Noor, so the final result is 3. The helper approach is transparent and works in older Excel versions, although it needs an extra column and must be filled down when new rows are added.
To mark the first occurrence of each value within each category, use a two-column helper. Assume categories are in A2:A100 and items are in B2:B100; enter =IF(OR(A2="",B2=""),0,IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,1,0)) in C2 and fill down. COUNTIFS checks whether that category-and-item combination has appeared earlier. The first nonblank occurrence gets 1, while repeat combinations get 0. A SUMIFS can then add those flags for a chosen category. This counts unique item-category pairs, not unique items across the whole sheet.
Choose the right method for your worksheet
For a one-off calculation in Microsoft 365 or Excel 2021+, the UNIQUE and FILTER formula is usually the clearest option. It updates when the source data changes and does not require manually removing duplicates. A helper column is a good fallback for older versions, for auditing which rows count as first occurrences, or when colleagues need to see the logic row by row.
- Need the number of different values? Use UNIQUE, filtering out blanks if they should not count.
- Need different values within a group? Filter by the group condition before applying UNIQUE.
- Need values that appear only once? Compare each distinct value’s COUNTIF result with 1.
- Need compatibility with older Excel? Use a first-occurrence helper column, or consider a PivotTable for a summary.
A PivotTable can summarize distinct values in some Excel editions, but the exact distinct-count option depends on how the PivotTable is created and the features available in that version. In Microsoft 365, the formula approach is often simpler for a live worksheet result. If you use a PivotTable, check whether the distinct count is available in the Value Field Settings for your data model rather than assuming every standard PivotTable offers it.
Common problems and details to check
Blanks and empty strings: Decide whether blank cells should count. The examples exclude them, including cells whose formulas return an empty string. If a blank is meaningful as its own category, remove the FILTER condition and consider how you want Excel to represent that blank in the result.
Text that looks like a number: A cell containing the number 25 and a cell containing text “25” can behave differently in formulas and data operations. If a count seems unexpected, check whether values are consistently stored as numbers or text. Converting the source data to one consistent type is often better than trying to compensate inside the distinct-count formula.
Spaces and inconsistent spelling: “Pen” and “Pen ” may be treated as different text because one has a trailing space. Likewise, misspellings or inconsistent labels create separate entries. Clean the source data first if those variations are accidental. TRIM can remove ordinary extra spaces, but applying cleanup may require a helper column or an adjusted formula.
Case and wildcard expectations: Standard Excel text comparisons are generally not case-sensitive, so values that differ only by capitalization are not a dependable way to create separate categories. Also, COUNTIF supports wildcard criteria such as an asterisk, while the equality test inside the FILTER example is a direct comparison, not a wildcard search. Do not substitute one method for the other without changing the logic.
Large ranges: Avoid using entire-column references in complex dynamic-array formulas unless you have a reason to do so. A bounded range such as A2:A5000 or an Excel Table column can make the calculation easier to maintain and avoid processing many unused cells. When the data grows, update the range or use a Table so added rows are included automatically.
Frequently Asked Questions
Can COUNTIF count unique values by itself?
Not directly. COUNTIF counts cells that meet a criterion, so repeated matches are counted repeatedly. Use UNIQUE with FILTER for a distinct count in newer Excel, or mark first occurrences with a helper column in older versions.
How do I count unique values in Excel based on another column?
Filter the value range using the condition range, then apply UNIQUE and count the result. For example, the formula in the conditional-count section counts distinct products in the region named in E2 and returns 0 if there are no matches.
Does the unique-count formula include blank cells?
The formulas shown for distinct values intentionally exclude blanks with a FILTER test. If blank entries should count as a category, adjust that filter and check the result in your version of Excel, especially if the source contains formulas returning empty strings.
What is the difference between unique and values that appear once?
A distinct count includes each value one time no matter how often it occurs. A count of values appearing once excludes anything repeated. A value appearing three times contributes 1 to a distinct count and 0 to the exactly-once count.
Do these formulas work on Mac or Excel for the web?
The UNIQUE and FILTER formulas work in supported Microsoft 365 and Excel 2021-or-later versions on Windows, Mac, and the web. The formulas themselves are the same, though regional settings can affect whether Excel expects commas or semicolons between arguments.
Related
- Conditional Formatting for Errors in Excel: How to Do It
- Contact List Excel Example and Setup Guide
- Excel Conditional Formatting Number Formats Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP With Multiple Criteria: Formulas and ExamplesTo look up a row that meets multiple criteria in Excel, multiply the criteria tests inside XLOOKUP: =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),C2:C100,"Not…
- SUMIFS in Excel: Multiple Criteria, Examples and TipsYes. In Excel, SUMIFS adds numbers only when multiple conditions are met; each criteria pair can test a…
- Excel XLOOKUP and Power Query: How They Work TogetherExcel XLOOKUP and Power Query work together, but they do different jobs: Power Query imports, cleans, and combines…
- SQL Server Split Function: STRING_SPLIT ExplainedSQL Server’s built-in split function is STRING_SPLIT, which turns a delimited string into a table of values. Use…
- X++ String Runtime Functions: How They Work and When to Use ThemX++ string runtime functions are built-in methods that let you manipulate, compare, and inspect text values at runtime…
- Excel LAMBDA: Check Whether a Cell Contains TextTo check whether cell A2 contains the word “lambda,” use =ISNUMBER(SEARCH("lambda",A2)); the result is TRUE if a match…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.