Google Sheets: Number of Used Rows in a Column

Google Sheets has several counting functions for counting up the number of cells in a selected range containing a specific type of data.

The COUNTA function counts the number of cells in a specified range that contain numbers, error values, dates, formulas, text, or Boolean values, while ignoring any blank or empty cells.

If data is later added to an empty cell in the range, the function updates the count automatically to include the revised data.

Take a look at this spreadsheet:

Click image to enlarge

Click image to enlarge

Suppose we want to total the number of email addresses in column B, placing the answer in cell E1.

We could use COUNTA with the following syntax:

=counta(B2:B100)

to return the count within cells B2 to B100. However, since we don’t know how many emails are listed (maybe more than 100), we need to express the range this way:

=counta(B2:B)

The formula will now total all the populated cells in column B starting at B2

Click image to enlarge

Click image to enlarge

As extra lines are added, the count in cell E1 will automatically increment. This can be particularly useful in sheets used to collect form data, as described previously in this post.

Here’s what happens where someone has failed to supply an email address:

Click image to enlarge

Click image to enlarge