How to use the COUNTA formula in Google Sheets

Written by Valentine Schelstraete

Jul 29, 2020

COUNTA formula in Google Sheets is a simple and widely-used formula for everyday spreadsheet work. It quickly gives you the total number of values within a specific range. Whereas the COUNT formula only counts cells containing numbers, the COUNTA function counts how many cells in the dataset contain any kind of data. That includes numerical values, text (including white space and zero-length strings), logical expressions, error messages, dates and formulas.

Repeated values are also counted. Blank or empty cells are excluded.

Syntax

COUNTA(value1, [value2, …])

  • value1 – the value or range of cell(s) to count within.
  • value2, value3 … – (optional, up to 30) additional values to also be included in the count.

The value arguments can be:

  • A value
  • A cell reference
  • A range of cells
  • A named range

How to use COUNTA formula

I will show the formula in action with a few examples:

COUNTA Formula: Usage

You can see that the input parameters can take many forms: a number, text within double quotes, and a reference to a range of cells.

No matter what kind of combination you use for the input parameters, the COUNTA formula does its job of counting the values. In all of these cases from row 2 through to 7, the formula returned the result 8, as there are 8 different values in the third column (including the “Failed” ones). Therefore, you can see that the formula counts both numbers and text values.

I included two column ranges in the case in row 8, hence the result was 16. But in the final example, you will see that the output returned is 3, while the data set consists of values from cells A9, A10, B9 and B10. This difference is because the COUNTA formula doesn’t count blank values!

COUNTA formula also counts other types of data:

COUNTA Formula in Google Sheets

In the example above, I entered various arbitrary values like a date, currency, percentage, decimal, and general text and even special characters. Accordingly, I have validated for blank values on the second column, using the ISBLANK formula. So, all the values in rows 2 through to 9 (barring row 8) are non blank values. And, not surprisingly, the COUNTA formula in column C counted only the non blank values.

Want to count how many unique values appear in a range? Use Google Sheets COUNTUNIQUE formula instead. 

Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

You may also like…

Share This