Lookup & Reference
Count Unique Numeric Values Separately From Unique Text Values in a Mixed Column
Count distinct numbers and distinct non-blank text labels independently in mixed-type ranges using UNIQUE, FILTER, ISNUMBER, and ISTEXT with empty-set guards.
Avoiding the COUNTA(UNIQUE(FILTER(...))) = 1 empty-result trap
Survey responses, lab logs, and ERP status columns often mix numeric readings (105, 220, 105) with text exceptions ("Pending", "Exempt", "Pending", or text-formatted "105"). Calling COUNTA(UNIQUE(range)) lumps numbers, text strings, and error/fallback strings together.
Filtering numeric rows via ISNUMBER and wrapping UNIQUE(FILTER(...)) in COUNT(...) safely returns 0 when no numbers exist (because COUNT ignores text/error fallbacks). For distinct text values, guarding COUNTIF(text_mask, TRUE) = 0 before calling COUNTA(UNIQUE(FILTER(...))) prevents a "no match" fallback string from being miscounted as 1 unique text item.
| Row Entry (A) | ISNUMBER (B) | Non-Blank ISTEXT (C) | Distinct Bucket Contribution |
|---|---|---|---|
| 105 (Number) | TRUE | FALSE | Unique Number #1 |
| "Pending" | FALSE | TRUE | Unique Text #1 |
| 220 (Number) | TRUE | FALSE | Unique Number #2 |
| 105 (Number) | TRUE | FALSE | Duplicate Number (ignored) |
| "Exempt" | FALSE | TRUE | Unique Text #2 |
| "Pending" | FALSE | TRUE | Duplicate Text (ignored) |
| "105" (Text) | FALSE | TRUE | Unique Text #3 (distinct from numeric 105) |
Count Unique Numeric Values Only
Filters A2:A8 to true numbers via B2:B8 and counts unique numeric values, returning 0 cleanly if no numeric cells exist.
=IF(COUNTIF(B2:B8, TRUE) = 0, 0, COUNT(UNIQUE(FILTER(A2:A8, B2:B8))))Count Unique Non-Blank Text Values Only
Filters A2:A8 to non-empty text strings via C2:C8 and counts distinct text entries with an explicit zero-match guard.
=IF(COUNTIF(C2:C8, TRUE) = 0, 0, COUNTA(UNIQUE(FILTER(A2:A8, C2:C8))))Try the example
Use the original values or change them to test your understanding.