CODEWITHSUNDEEP
google-sheetsgoogle-docs
steakpi
steakpi

Reputation: 953

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

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.

Upvotes: 70

Views: 125155

Answers (5)

saji89
saji89

Reputation: 2251

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

Upvotes: 0

pnuts
pnuts

Reputation: 59495

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.

Upvotes: 4

rawnuggets
rawnuggets

Reputation: 139

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

Upvotes: 6

CalamitousCode
CalamitousCode

Reputation: 1414

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.

Upvotes: 19

Stryder
Stryder

Reputation: 1421

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.

Upvotes: 119

Related Questions