Purpose

Return value

Syntax

=COUNTA(value1,[value2],...)
  • value1 - An item, cell reference, or range.
  • value2 - [optional] An item, cell reference, or range.

Using the COUNTA function

The COUNTA function counts cells that contain values, including numbers, text, logicals, errors, and empty text (""). COUNTA does not count empty cells.

The COUNTA function returns the count of values in the list of supplied arguments . COUNTA takes multiple arguments in the form value1 , value2 , value3 , etc. Arguments can be individual hardcoded values, cell references, or ranges up to a total of 255 arguments. All values are counted, including text, numbers, percentages, errors, dates, times, fractions, and formulas that return empty strings (""). Empty cells are ignored.

Examples

In the example shown, COUNTA is set up to values in the range B5:B15:

=COUNTA(B5:B15) // returns 9

COUNTA returns 9, since there are 9 non-empty cells in the range B5:B15.

The COUNTA function counts numbers and text:

=COUNTA(1,2,3) // returns 3
=COUNTA(1,"a","b") // returns 3
=COUNTA(1,2,3,"a",5%) // returns 5

To count non-empty cells in the range A1:A10:

=COUNTA(A1:A10) // count non-empty cells in A1:A10

To count non-empty cells in the range A1:A10 and the range C1:H2:

=COUNTA(A1:A10,C1:H2) // two ranges

Empty strings

Note that COUNTA includes empty strings ("") in the count, which can be returned by formulas. For example, the formula below will return “OK” when the value in A1 is at least 10, and an empty string ("") when the value is less than 10:

=IF(A1>=10,"OK","")

The COUNTA function will count both results as non-empty. To ignore empty strings, this example provides a workaround .

Invisible characters

Be aware that COUNTA will also count cells that look empty, but actually contain invisible characters or an empty string ("") returned by a formula. You can check which cells are blank using Go To > Special > Blanks:

  1. Select a range
  2. Open Go To dialog (Control + G)
  3. Press “Special”
  4. Select “Blanks”

Functions for counting

  • To count numbers only, use the COUNT function .
  • To count numbers and text, use the COUNTA function .
  • To count with one condition, use the COUNTIF function
  • To count with multiple conditions, use the COUNTIFS function .
  • To count empty cells, use the COUNTBLANK function .

Notes

  • COUNTA will count cells that contain numbers, text, logical values, error values, and empty text ("").
  • To count numeric values only, use the COUNT function .

Purpose

Return value

Syntax

=COUNTBLANK(range)
  • range - The range in which to count blank cells.

Using the COUNTBLANK function

The COUNTBLANK function returns a count of empty cells in a range. Cells that contain text, numbers, errors, spaces, etc. are not counted. Formulas that return an empty string ("") are counted as blank. COUNTBLANK takes just one argument, range, which must be a cell range .

Examples

In the example shown, the formula in cell E6 is:

=COUNTBLANK(B5:B15) // returns 3

COUNTBLANK returns 3 since there are 3 blank cells in the range. Note that cell B12 is not included because it contains a space character (" “). Cell B13 contains a formula that returns an empty string:

="" // formula in B13

COUNTBLANK considers B13 blank and includes it in the count.

Formulas that return empty strings

The IF function is often used to return empty strings. For example, if A1 contains 21, this formula in B1 will return an empty string:

=IF(A1>30,"Overdue","")

The idea is that that cell B1 should be empty unless the value in A1 is greater than 30. The COUNTBLANK function will indeed count B1 as empty when the value in A1 is less than or equal to 30. However it’s worth noting that COUNTA and COUNTIFS will count B1 as not empty in the same case. In other words they will see the empty string (”") returned by IF as not blank .

Invisible characters

Some cells look empty, but actually contain invisible characters. To check which cells are blank use Go To > Special > Blanks:

  1. Select a range
  2. Open Go To dialog (Control + G)
  3. Press “Special”
  4. Select “Blanks”

Functions for counting

  • To count numbers only, use the COUNT function .
  • To count numbers and text, use the COUNTA function .
  • To count with one condition, use the COUNTIF function
  • To count with multiple conditions, use the COUNTIFS function .
  • To count empty cells, use the COUNTBLANK function .

Notes

  • Cells that contain text, numbers, errors, etc. are not counted.
  • Cells with formulas that return an empty string ("") are counted.
  • Cells that contain only a single quote (’) are counted.
  • Cells that contain zero are not counted.