Skip to content

Count unique values

Count how many different values a column contains.

The formula

Argument separator
Excel
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&""))
Google Sheets
=COUNTUNIQUE(A2:A100)

How it works

  1. COUNTIF(A2:A100, A2:A100&"")

    For each cell, how many times its value appears.

  2. (A2:A100<>"")/…

    Each value contributes 1 divided by its count, so every distinct value adds up to exactly 1. Blanks add 0.

  3. SUMPRODUCT(…)

    Adds it all up. Works in every Excel version.

Example

A: CustomerResult
Acme3
Globex
Acme
Initech

Tips

  • In Excel 365 a shorter version is =COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>""))).