Count how many distinct customer names appear in column A, ignoring blanks

公式

=COUNTA(UNIQUE(FILTER(A:A, A:A<>"")))

说明

FILTER drops blanks, UNIQUE keeps each name once, and COUNTA counts the spilled list. On Excel 2016 use the SUMPRODUCT variation instead.

步骤

  1. FILTER keeps only non-empty cells in A.
  2. UNIQUE removes repeated names.
  3. COUNTA counts the remaining items.

变体

Excel 2016 / WPS

Classic unique-count without dynamic arrays. Limit the range for speed.

=SUMPRODUCT((A2:A1000<>"")/COUNTIF(A2:A1000, A2:A1000&""))

Unique values that meet a condition

Counts distinct names in region West.

=COUNTA(UNIQUE(FILTER(A2:A1000, B2:B1000="West")))

Case-insensitive unique count

Treats Acme and ACME as the same customer.

=COUNTA(UNIQUE(LOWER(FILTER(A2:A1000, A2:A1000<>""))))