It can be frustrating when our Google Sheets functions don’t go the way we expect them to.
In this quick tutorial, we cover two common reasons why your COUNT function might not be running the way you expect it to. One beginner issue and one a little more advanced.
Counting Characters
When you count a range of numbers you use the COUNT function. However, if you are counting all the characters in a range you need to use the COUNTA function.
COUNTA counts all the values in a dataset including:
- All duplicate values (You can use COUNTUNIQUE for just unique values).
- Cells containing white space. That is, cells with spaces in them but no text.
- Cells with a text string of zero. This is usually generated as a result of a formula.
To use COUNTA simply select a range or set of ranges.
=COUNTA(Range)
=COUNTA(C2:C25)
Counting the wrong number of values in a range generated from formulas
Quite often we are trying to template our Google Sheets for reuse in other projects or tasks. Take a look at this example:
Here in Column K we have a formula that joins the first and last name together from columns I & J if it sees text in column J otherwise it will return an empty string. Here is the formula:
=IF(J2 = “”,“”, JOIN(” “,I2:J2))
This formula is then dragged all the way down the column so that it can be used again for another project.
The problem
The problem is that if we try and use COUNTA to get the values in Column K we will return a wrong number.
Why?
Because COUNTA will also count any cell that contains a string of characters with a length of zero (“”). So this will include and count all the cells in our range that contain our formula where we don’t want to display a value.
So how do we fix this?
The solution
Option 1
The most logical option is to try and use another column to reference your count. Unfortunately, this isn’t always possible.
Option 2
If your formula returns an error on an empty cell and you want to hide it, you could use the IFERROR function instead of the IF("","",... option to create an empty cell.
Here you wrap your formula inside the IFERROR function. You then have an optional argument to add what to do if there is an error or leave it blank if you don’t want anything to appear in the cell.
=IFERROR( your formula)
When you run a COUNTA on this column now it will return the correct count.
Option 3
If you can’t do either of these options then you could create a formula that checks the length of each string of data in each cell and if that cell length is greater than zero, count the cell.
First, you can get the length of a cell with the LEN function that takes a cell value. In our example, we will use the first cell item in our full name column K.
=LEN(K2)
We need to be able to use this LEN value on the selected range that we want to count. Here, we can use the ARRAYFORMULA function. This will allow us to put in a range of values in our LEN formula and get the length of each one. The result will be an array containing the length of values for each cell in the range.
=ARRAYFORMULA(LEN(K2:K))
Finally, we can use COUNTIF to count all the values in our new array of lengths that are greater than zero.
=COUNTIF(ARRAYFORMULA(LEN(K2:K)),“>1”)
Conclusion
Check out the video below for more information.
Do you have a clever way of dealing with this problem? I would love to hear about it in the comments below.
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
~Yagi
