Summary

The Excel COUNTA function returns the count of cells that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells.

Purpose 

Count the number of non-blank cells

Return value 

A number representing non-blank cells.

Syntax

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

How to use 

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

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.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.