Using the COUNTA Function in Google Sheets

- Written by Puneet

excelchamps-free-courses-puneet-gogia

Counting the values is one of the most important calculations in Google Sheets or any other spreadsheet application. In Google Sheets, we have a lot of functions that can help you count, but of those, COUNTA is the first and most important function.

counta-function-in-google-sheets

In this tutorial, we will learn to use COUNTA in detail. We will also learn the best situations where we need to use it and then some of the important points to take care of while using it.

What Does COUNTA Do?

COUNTA counts non-empty cells. In simple words, when you refer to a range of cells, it returns a count of all the cells where you have value. Non-blank cells contain text, numbers, dates, formulas, or error values. In COUNTA, you need to refer to a range of cells or multiple cells as a separate reference.

Syntax

COUNTA(value1, [value2], ...)

The above syntax says value1, [value2], which refers to the cells or a range of cells from where you want to get the count. There are multiple ways to refer to the cells in their function, which is what we will learn in the next example.

Basic use of COUNTA

In the example below, we have values in the range A1:A3, the same range we specified in the COUNTA. When you hit enter to get the result, it returns 3.

returned-value-using-counta

You can see in the example above we have three values: one is a date, the second is a text, and the third is a number. And counting all of them, it has returned 3 in the result as it can count all the three types of values. If you change the range from A1:A4, the result still shows the same count of values.

counta-counted-all-value-types

This is because there is no value in cell A4, and COUNTA can only count the cells with a value.

Using Multiple Ranges in COUNTA

Like the following example, you can also refer to more than one range in the function. Here, the formula counts how many cells are not empty in the ranges A1 to A3 and B1 to B3. You can add as many ranges as needed, separated by commas.

use-multiple-ranges-in-counta

Even though you can refer to multiple cells as a single entity in the function, in the example below, you refer to the same range of cells, but each cell has been referred to as a separate entity. And the count in the result is the same.

refer-multiple-cells-as-single-entity

But I’d suggest you refer to an entire range instead of single cells, which takes a lot of time and is tough to edit further if needed.

Although it has been said that COUNTA can take 255 arguments, I have tested and found that it can take more than 255 arguments while calculating the non-empty cells.

In modern versions of Excel, Google Sheets, or any other spreadsheet application, the limit on the number of arguments is much higher, often limited by the available memory rather than a strict count limit.

Problems You Might Face

COUNTA is a tiny but powerful statistical function that you can use to count values, but a few situations make you face a few problems while using it. If you use the COUNTA function with an incorrect range, such as a range that does not exist or is not correctly defined, it returns an #ERROR in the result.

errors-while-using-counta

In the above example, we have referred to a range that does not exist or is quite outside the sheet’s limits, which is why it returns an error.

Now, this problem is BIG; if you have a cell where you have a white space in cell and then you use the COUNTA to count the non=blank cells, the function will count the

cell-with-white-space

In the above example, you can see five cells with values, but the result is 6. In cell B3, whitespace gets counted in the COUNTA along with other values. This is one of those cases where you don’t have any solution with COUNTA.

So, you need to use a combination of the functions to help you get the correct count of cells in the result. See the below formula:

combine-counta-function
=ARRAYFORMULA(COUNTA(IF(TRIM(A1:A10) <> "", A1:A10,)))

This formula counts non-empty cells in the range A1 to B3, ignoring those that contain only spaces. TRIM removes spaces, and IF checks if cells are not empty after trimming. ARRAYFORMULA ensures this works for the whole range as an array formula.

Using COUNTA with Other Functions

One thing I really like about COUNTA is that it can be used with other functions without any problems. Below, I have shared some examples of using it with other functions to do more complex calculations.

1. COUNTA with IF

The below formula counts non-empty cells in A1 only if the corresponding cell in B1 contains “Yes”.

=COUNTA(IF(B1:B10 = "Yes", A1:A10))

2. COUNTA with UNIQUE

It counts the number of unique non-empty values from A1 to A10. First, UNIQUE returns a list of unique values, and the COUNTA gets the count of values, ignoring blank values.

=COUNTA(UNIQUE(A1:A10))

3. COUNTA with FILTER

This formula first uses the FILTER function to create an array of the range A1, only including cells with the corresponding value in B1 greater than 10. Then, COUNTA counts the number of non-empty cells in the filtered array created by the FILTER.

=COUNTA(FILTER(A1:A10, B1:B10 > 10))

COUNTA Vs. COUNT

As we have seen in the examples, COUNTA has a specific usage than COUNTA. But here are some of the few notable differences. You can note down how to use both functions better.

  • COUNTA counts all the non-empty cells, including those with text, numbers, formulas, and errors, while COUNT only counts cells with numbers (dates also, as Excel stores date as numbers).
  • Both functions ignore empty cells, but COUNTA will include cells with spaces, while COUNT will not. Refer to the example we have used above.

The example below shows that when we use COUNTA, the result is 5; when you use COUNT for the same range of cells, the result is 4, as the value in cell A2 is a text.

counta-vs-count

Well, you can’t say that one function is better than another. Both functions have different usages, and both are useful.

COUNTA Vs. COUNTIF

As you have learned, COUNTA counts all cells that contain data. It doesn’t matter if the cell value is a number, text, or even just a space; if it isn’t empty, it gets counted.

On the other hand, COUNTIF counts cells that meet a specific condition you set. For example, if you want to count how many cells contain the word “Yes” or numbers greater than 10, you use COUNTIF. It’s more focused and only counts the cells that fit your criteria.

Do you remember the problem I shared about having whitespace in a cell that COUNTA counts and shows wrong results? COUNTIF can solve that problem.

Leave a Comment