Why your COUNT function might not be working in Google Sheets

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:

Example of empty string in a cell Google Sheets
Click to Expand!

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.

Hire me for our next Google Workspace 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.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

=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.

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

=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.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

One thought on “Why your COUNT function might not be working in Google Sheets”

  1. Hey there, I’m trying to sum values between a specific range of rows (for instance, R13:R177). The rows in this range can differ since I’m dragging rows into and outside this range. It’s very similar to the COUNTA formula, but instead of counting the cells containing any type of information, I want to sum the numbers in these cells. For example, cell R14 is 1000 and cell R15 is 2000. The formula should show 3000. If I drag row R14 outside the range R13:R177 then the formula should show 2000. Thanks a lot for your help.

Leave a Reply