How To: Google Sheets Count If Not Blank

Google Workplace is an online substitute for Microsoft’s Outlook, and it counts several different services - Docs, Slides, Sheets, Drive, and more.

Google Sheets is nearly the same as Microsoft’s Excel, allowing users to create an online shareable version of their sheets and files.

If you use Excel or Sheets on a daily basis, you’ve surely noticed that, at times, some of the cells and columns are blank and contain no data.

Having cells with no data can cause issues when doing calculations, and the best option would be to count all cells, except for the one that contains no information.

Luckily, Sheets has a function allowing just that - COUNTA. The COUNTA function is used for counting cells that contain data; however, when some of the cells contain special characters, you might run into issues.

If you’re unsure of how to use the COUNTA function and wish to organize your Sheets quickly, check out the step-by-step process below.

You might also like: How to Separate First and Last Name in Google Sheets.

What is the COUNTA function in Sheets?

The COUNTA function in Google Sheets counts the number of cells within a certain range that contain data - values, or a text string.

While using this function, the cells that do not contain any data are not taken into account.

How do I use the COUNTA function in Sheets?

Follow these steps to count the number of cells that contain data and are not blank in Google Sheets:

1. Select a blank cell, and type the =COUNTA function, which will include the range of cells that need to be counted, for example, =COUNTA(A1:A13), as in the photo above.
2. Press enter and the COUNTA function will automatically make the calculations in the selected cell range.

In the D12 empty string, the count function formula shows up, along with the number of empty cells in Google Sheets.

Note: The COUNTA function is the most commonly used one, and is the easiest method of counting cells that are blank and contain no data. However, in the case of using conditional formatting that makes the cell seem blank, even when it isn't, the calculations will not be correct. In such instances, it's recommended to use the SUMPRODUCT function.

Check out: How to Remove Gridlines in Google Sheets.

How do I use the SUMPRODUCT function to count the non-empty cells?

To avoid wrong calculations, use the SUMPRODUCT function. Follow these steps:

1. Click on an empty cell, and enter the =SUMPRODUCT(--LEN(range)>0)) function.
2. This will count the cells that do not appear blank. The LEN function will return a value bigger than zero, while also counting the number of characters that appear in the sheet.
3. Click enter, and the number of cells will show up automatically.

Conclusion

Google Sheets offers numerous functions which can help you organize your data easily, and make correct calculations that would normally take hours to finish if you do them manually.

In this article, we covered two different methods to count non-blank cells in Google Sheets.

If you liked this article, check out: How to Assign in Google Sheets and How to Combine First and Last Names in Google Sheets.