COUNT, COUNTA and COUNTIF

Every example in this lesson uses this sales sheet. Notice that D6 is empty:

COUNT, COUNTA and COUNTBLANK

=COUNT(D2:D8)        → 6    cells containing numbers
=COUNTA(C2:C8)       → 7    cells that aren't empty (text, numbers, anything)
=COUNTBLANK(D2:D8)   → 1    empty cells

A classic surprise: COUNT on a column of names gives 0, because names aren't numbers. Use COUNTA to count entries of any kind.

COUNTIF: count what matches

=COUNTIF(B2:B8, "North")     → 3
=COUNTIF(D2:D8, ">1000")     → 3
=COUNTIF(C2:C8, "Asha")      → 3

COUNTIF(range, criteria) counts the cells in a range that meet one condition.

Writing criteria

=COUNTIF(C2:C8, "A*")        → 3     Asha, three times
=COUNTIF(B2:B8, "<>South")   → 5

To compare with a value in another cell, join the operator to the reference with &. If F1 contains 900:

=COUNTIF(D2:D8, ">"&F1)      → 4     1200, 2300, 950 and 1600

Writing ">F1" would look for values greater than the text "F1", which matches nothing.

COUNTIFS: several conditions

=COUNTIFS(B2:B8, "North", D2:D8, ">500")      → 2
=COUNTIFS(C2:C8, "Asha", D2:D8, ">=1000")     → 2

COUNTIFS takes pairs of range, criteria, and counts only the rows where every condition is true. Here, of North's three sales, two are over 500; and of Asha's three sales, two are at least 1000.

Counting unique values

In Microsoft 365 and Excel 2021:

=COUNTA(UNIQUE(C2:C8))       → 4     Asha, Leo, Mei and Raj

UNIQUE lists each distinct value once, and COUNTA counts them.