Google Sheets formula builder
Count Unique Formula Builder
Create a robust distinct-value count formula for Google Sheets or modern Excel without counting blank cells.
Builder inputs
Use a one-column range that contains names, owners, IDs, SKUs, or categories.
=IFERROR(COUNTUNIQUE(FILTER(B2:B100, B2:B100<>"")), 0)=IFERROR(COUNTA(UNIQUE(FILTER(B2:B100, B2:B100<>""))), 0)The IFERROR wrapper returns 0 when FILTER finds no nonblank values.How this formula works
- Google Sheets uses COUNTUNIQUE to count distinct values.
- Modern Excel uses UNIQUE plus COUNTA for the same dynamic-array pattern.
- Wrapping the filtered count in IFERROR prevents empty-source errors from showing to users.
Sample data
| Task | Owner | Status | Due Date |
|---|---|---|---|
| Import leads | Maya | Complete | 2026-01-06 |
| Clean headers | Nico | In Progress | 2026-01-08 |
| Review budget | Maya | Complete | 2026-01-12 |
| Publish report | Iris | Blocked | 2026-01-15 |
Common mistakes
- Blank cells can be counted unless you filter them out.
- Names with hidden spaces count as separate unique values.
- Older Excel versions without dynamic arrays need a different approach.
Related formulas
FAQ
Are these formulas generated with AI?
No. The builder uses deterministic TypeScript functions in the browser and does not call any AI API.
Do I need to upload my spreadsheet?
No. Enter ranges and criteria manually. The site does not upload, store, or process spreadsheet files.
Can I copy the generated formula?
Yes. Each output includes a copy button so you can paste the formula into Excel or Google Sheets.