The COUNTA function counts the non-blank cells in a specified range. You can use it to find the number of cells containing text, numbers, images, boolean values, errors, and more.
When all inputs to the function are numbers, it produces the same result as the COUNT function. However, when the inputs are text or boolean values, the COUNTA function acts differently than COUNT. Get your copy of the template used in this tutorial to follow along.
- 1 Purpose
- 2 Syntax
- 3 Similar Functions
- 4 Examples
- 5 Notes
- 6 Related Tutorials
The COUNTA function returns the number of non-blank cells in a data set.
=COUNTA(value1, [value2, ...])
value1– The first value or range to consider while counting.
value2, …– [OPTIONAL] Additional values to consider.
COUNT – Count the numeric values in a data set
COUNTA – Count the non-blank values in a data set
COUNTIF – Count the cells that match a criterion
COUNTIFS – Count the cells that match multiple criteria
COUNTUNIQUE – Count the unique values
COUNTUNIQUEIFS – Count the unique values that meet multiple criteria
COUNTBLANK – Count the blank cells
Example 1 – Counting Numbers
Four numerical values are in the image above, and the formula returns the number . Counting numbers is a straightforward use of the function.
Example 2 – Counting Blanks vs. Counting Zeroes
The COUNTA function will treat blanks differently from zeroes as we move into the following example. A blank cell does not have a data type, but
0 is a number.
0 is a number, you may not intend to count it. If you only want to count numbers greater than zero, you may consider using a formula such as
What if we had a mix of different data types?
Example 3 – Counting Mixed Data Types
The COUNTA function sees a value in every cell in the range
Example 4 – Counting More Data Types
COUNTA will recognize more than just text, images, boolean values, and numbers.
The COUNTA function does not care if a date is valid. Unlike COUNT, Google Sheets includes correct and invalid dates in its calculation.
Let’s take a closer look at non-printing characters like the space in
Example 5 – Counting Non-Printing Characters
Invisible characters (AKA non-printing) such as spaces can be a hassle when importing data from other sources.
Even though you can’t see them, they are text characters and, therefore, the function will count them as shown above.
Live Examples in Sheets
Go to this spreadsheet for examples of the COUNTA functions shown above that you can study and use anywhere you would like.
- COUNTA does not count blank cells.
- The COUNTA function counts every cell with any content as 1.
- Consider using the SUBTOTAL function if you have multiple counts in one column.
Three techniques to count unique values based on specified criteria.
Count unique values in a spreadsheet while ignoring capitalization.
Count cells based on several conditions