When analyzing data in spreadsheets, you often need to know how many distinct items exist in a column. An excel formula to count unique values helps you quickly measure true diversity, such as the number of unique customers or product codes in a list.
Below is a quick reference that explains common approaches, their behavior with blank cells, and how to combine them with filters or conditions.
| Method | Formula Example | Handles Blanks | Notes |
|---|---|---|---|
| SUMMARIZE + ROWS | =ROWS(SUMMARIZE(A:A,A:A,"Distinct")) | Counts blank as one item | Works in modern Excel, dynamic array aware |
| SUMPRODUCT + COUNTIF | =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)) | Requires clean data, blanks cause errors | Classic array style, compatible with older versions |
| FILTER + COUNTA | =COUNTA(FILTER(A2:A100,A2:A100"")) | Excludes blanks intentionally | Pairs well with criteria for conditional subsets |
| UNIQUE + ROWS | =ROWS(UNIQUE(A2:A100)) | Counts blank as one item | Requires dynamic arrays, spills results |
Use SUMPRODUCT and COUNTIF for Flexible Array Formulas
The SUMPRODUCT and COUNTIF combination remains a reliable way to count unique text and numeric entries in a column. This pattern divides 1 by the frequency of each value, so each distinct item contributes exactly 1 to the total.
Place the formula on a helper column or a dashboard cell, pointing at your data range. It ignores structured table headers when you adjust the range, but be careful with completely blank cells, which can create division errors that break the calculation.
Wrap the core logic inside an IF or filter to suppress errors when blanks exist. This keeps results stable across weekly updates as new rows are appended below your original table range.
Leverage the UNIQUE Function for Simplicity
The UNIQUE function provides a transparent path to count unique values in modern Excel. By feeding it a column range, you receive a spill array of unique items that you can immediately pass to ROWS.
This approach visually confirms which items appear, making it easy to audit duplicates versus truly distinct entries. It automatically expands when source data grows, provided your worksheet has enough empty cells beneath to host the spill.
Remember that UNIQUE treats a single blank cell as one distinct result, so adjust with additional FILTER logic if you want to exclude blanks entirely from your count.
Combine FILTER and COUNTA for Clean Distinct Counts
Using FILTER together with COUNTA lets you count unique values while excluding empty cells. You first filter the column to non-blank rows, then apply COUNTA to the resulting array to measure diversity.
This pattern is helpful when you need a distinct count based on partial criteria, such as entries from a specific date range or region. Nest another layer of FILTER to add conditions without complicating the core formula.
Because FILTER returns an array, it works smoothly with dynamic dashboards, updating instantly when source data is refreshed or new records are pasted into the table.
Apply Advanced Criteria with SUMPRODUCT and COUNTIFS
When you need to count unique values under conditions, extend SUMPRODUCT with multiple COUNTIFS segments. This variation supports ranges that share the same dimensions and lets you restrict counting to rows that meet specific rules.
For example, you can count distinct customers who bought more than once in a month or filter product codes by warehouse location. Keep ranges aligned and avoid entire column references to maintain calculation speed and precision.
Test the formula on a small dataset first, because array division logic inside SUMPRODUCT can amplify errors if misaligned ranges or unexpected blanks appear in criteria columns.
FAQ
How do I count unique values excluding blanks using a simple formula?
Use =ROWS(UNIQUE(FILTER(A2:A100,A2:A100""))) to count distinct items while ignoring empty cells.
Can I count unique text values in a column with case sensitivity?
Excel native formulas like UNIQUE and COUNTIF are not case-sensitive; for case-sensitive distinct counts, use SUMPRODUCT(--(1/COUNTIF(A2:A100&A2:A100,A2:A100&A2:A100))) or a helper column with hashed values.
What is the fastest method to count unique values in a very large column?
PivotTable distinct counts or Power Query are generally faster than array formulas on large data, as they use optimized engines and avoid volatile recalculation overhead on every change.
How can I count unique values only when another column meets a condition?
Build a filtered array in a helper column or use =SUMPRODUCT(--(FREQUENCY(IF(B2:B100="Yes",MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1)>0)) to count distinct items in A only where B equals "Yes".