The COUNTA function is used to count large amounts of cells with varying data types in a specific range. As long as there is data in a cell, it can record it! But how do you use it? How is it different from the COUNT function? This guide will tell you all you need to know about this versatile tool and its various uses in Google Sheets.
What is the Google Sheets COUNTA Function?
The COUNTA function gives you the total number of values within a given range. Along with COUNT and COUNTIF, it is part of the COUNT family.
You can use it to count a range of cells within Google Sheets with many different data types. It can also count cells in multiple ranges. As long as a cell has any sort of data in it, it will count it as having data.
Here is an example of counting cells with mixed data:
As you can see, Disney’s assets include theme parks, a streaming service, and around $20 billion. Of those assets, COUNT would only record the numerical value of cash.
It’s most commonly used for:
- Grading (especially for teachers).
- Data involving dates and/or times. COUNT will not count them.
- Any data involving numbers with comments.
- Counting someone’s financial assets (which may extend beyond hard cash or have fluctuating value).
The only thing this formula does not count inside of Google Sheets is completely empty cells.
Function Syntax
The formula for it in Google Sheets is:
=COUNTA(value1, [value2, value3, etc…])
The values can be anything as long as something is in the chosen cell or range. If a cell has any character in it, that will count as data.
In short:
Value 1 = the value or range of cells you want to count in.
Value 2, 3, etc. = any extra values (up to 30) included in the count.
You can also use it to count multiple columns of cells with mixed data. When you need to count multiple columns, your formula will look more like =COUNTA(A1:A10, B1:B10). This will calculate the amount of data in all columns.
How to Use the COUNTA Function
Step 1: Click the cell where you want your result to appear.
Step 2: Type =COUNTA(
Step 3: Insert Range 1 into the formula (e.g. A2:A9).
Step 4: Add a comma, then insert another range.
Step 5: Close your formula with another parenthesis.
Step 6: Press Enter, or click away from the cell to see the result. You’re done!
Examples
Below are some examples of this formula in action.
Using the COUNTA Function to Count Assets
Let’s try using this feature to summarize the assets of a popular celebrity—in this case, TV personality Richard Rawlings. Where do we begin?
This formula counted all 7 cells in the column, despite different fonts, text, and numbers that do not go together. Jumbles of data like this are great reasons to use this feature!
The COUNTA Function in Multiple Ranges
Next, let’s try to use the same equation to count all of the cells describing Rawlings’s past and present assets. We will copy the formula from before, then add “C2:C9” as a range. To distinguish this count from the last, we will put it in cell E2.
Note how the result did not count C4, the completely blank cell. For now, we do not know the status of Rawlings’s truck, so it does not come up in the total.
But let’s add a space in C4 and see what happens to our total:
This formula will count anything— even a stray tap of the spacebar. (For the rest of this guide, we will undo that space. It is an example of what not to do.)
Be careful when using it, because if you let it count a cell with a space in it, it may throw your data off.
COUNTA function VS COUNT in Google Sheets
Spreadsheet masters may have noticed that this feature is very similar to COUNT. The biggest difference is that this feature counts any cells with data, while COUNT only counts cells that only have numerical data.
Let’s look at an example of how these two equations work on the same set of data. We will add =COUNT(A2:A9) to cell F1.
Note that COUNT only counted the numeric value with a dollar sign in A6. It did not matter that the other cells had numbers. COUNT did not care about them because they had non-numerical data.
COUNT is more restrictive. You can use COUNT if your data set contains only numbers, but use COUNTA function if you want to count cells with numbers and other data.
COUNTIF in Google Sheets
But what if you want to be more selective? That is when you use COUNTIF. COUNTIF lets you filter your results based on certain criteria—in this case, the assets that Richard Rawlings still owns. COUNTIF looks for something specific in that range, which appears after the comma (for example, the word “yes” in C2).
We want to tell the formula to look for how many things on the sheet are still owned by Rawlings. In this case, we will tell COUNTIF to count the word “yes.”
With this formula, we have determined that Richard Rawlings still owns 5 out of the 8 assets mentioned in column A. Again, this does not register the blank space in C4, but it does not matter with COUNTIF. What matters to the COUNTIF formula is the word “yes.”
While it is powerful, if you want the best image of your data, you may wish to combine it with other formulas. And if your data is made only of numbers (with no text), COUNT may be the better equation to use.
Advantages and Disadvantages
This function is a powerful tool in Google Sheets. There are still reasons to use formulas like COUNT for the basic addition of large amounts of strictly numerical data, but it will cover more abstract assets as well. But if you wish to narrow your results down, you may want COUNTIF instead.
Overall:
Advantages:
- Good for counting cells with mixed data.
- Can count multiple columns.
- More versatile than COUNT.
Disadvantages:
- Adds cells, not numbers.
- Does not count blank spaces. Make sure blank cells are truly blank.
Conclusion
The job of this function is to count any cells you want as long as they have data in them. This is handy for data that mixes numbers and text. But be careful; small marks and spaces count as data, too.
That said, this method is not optimal for all sets of data. If your data set consists entirely of numerals (and symbols like $), COUNT may be better. If you want to narrow your data down, COUNTIF may be the best fit. It is up to you to pick the right tool for the job.
Recommended articles
More resources
