Examples and comparison of the counting functions

Assuming that the whole of column A and rows 11 to 65536 are empty, using the counting functions on the ranges B1:B10 and A1:A10 gives these results:

COUNTA counts all non-empty cells:
=COUNTA(B1:B10) gives 6
=COUNTA(A1:A10) gives 0

COUNT counts all numbers:
=COUNT(B1:B10) gives 3
=COUNT(A1:A10) gives 0

COUNTROWS2 counts the number of rows before the last empty cell: =COUNTROWS2(B1) gives 8
=COUNTROWS2(A1) gives 1
(always counts the referenced cell even if it is empty).

COUNTUSEDROWS2 counts the number of rows to the last row in the used range:
=COUNTUSEDROWS2(B1) gives 9
(B9 is empty but used because it is formatted)
=COUNTUSEDROWS2(A1) gives 9
(although column A is empty the last used range row is 9)

COUNTCONTIGROWS2 counts the number of rows before the next empty cell:
=COUNTCONTIGROWS2(B1) gives 3
(always counts the referenced cell even if it is empty)
=COUNTCONTIGROWS2(B5) gives 4
=COUNTCONTIGROWS2(A1) gives 1
(Always counts the referenced cell even if it is empty)

COUNTBLANK counts the number of blank or empty cells: =COUNTBLANK(B1:B10) gives 4
=COUNTBLANK(B1:B10) gives 10

COUNTROWS2 is faster than COUNTA when counting a single column.

Copyright © 2023 Decision Models Ltd