Count number of cells with any value (string or number) in a column in Google Docs Spreadsheet

Question

I have several columns in Google Spreadsheet.

I would like to count how many cells have a value (number or string) and display that number at the top of the column.

For example:

---

Hello

---

World

---

123

---

Some string

---

The above column would give me the answer of "4"

I have not managed to find a formula that does this.

Answer 1

In the cell you want your result to appear, use the following formula:

=COUNTIF(A1:A200,"<>")

That will count all cells which have a value and ignore all empty cells in the range of A1 to A200.

Answer 2

You could also use =COUNTA(A1:A200) which requires no conditions.

From Google Support:

COUNTA counts all values in a dataset, including those which appear more than once and text values (including zero-length strings and whitespace). To count unique values, use COUNTUNIQUE.

Answer 3

An additional trick beside using =COUNTIF(...) and =COUNTA(...) is:

=COUNTBLANK(A2:C100)

That will count all the empty cells.

This is useful for:

• empty cells that doesn't contain data
• formula that return blank or null
• survey with missing answer fields which can be used for diff criterias

Answer 4

Shorter and dealing with a column (entire, not just a section of a column):

=COUNTA(A:A)

COUNTA

Beware, a cell containing just a space would be included in the count.

Answer 5

The SUBTOTAL function can be used if you want to get the count respecting any filters you use on the page.

=SUBTOTAL(103, A1:A200)

will help you get count of non-empty rows, respecting filters.

103 - is similar to COUNTA, but ignores empty rows and also respects filters.

Reference : SUBTOTAL function