When creating a list, such as a grocery list, it is essential to keep count of your items. However, if your data table contains blanks in between, making this count can get a bit difficult.
Once you select a range, Google Sheets will temporarily display the number of non-empty cells in the bottom-right corner of the browser. The count will disappear when you deselect the range.
If you want to generate the total number of non-empty cells on your spreadsheet, use the COUNT and COUNTA functions. While COUNTA displays the total number of all non-empty cells, the COUNT function calculates the count of cells containing only numbers.
Aside from these functions, you can also create formulas using COUNTIF, COUNTBLANK, SUMPRODUCT, LEN, and TRIM to count non-blank cells in Google Sheets.
Count Cells All Non-Blank Cells
| Function Used | Date Type | Syntax |
| COUNTA | STATISTICAL | =COUNTA(value1, [value2,..]) |
The COUNTA function counts all cells that are not empty in Google Sheets. This includes numbers, texts, strings, and even hidden characters.
The following spreadsheet contains the favorite movies of 15 individuals.
However, not everyone has filled out the list. As you can see, some movies, like 1945 and 8 ½, are in a number format. This is the perfect opportunity to use the COUNTA function!
On cell B19, we entered the following formula:
=COUNTA(B4:B18) // Counts all non-empty cells in range B4:B18.
Count Cells with Numbers
| Function Used | Data Type | Syntax |
| COUNT | STATISTICAL | =COUNT(value1, [value2,...]) |
If your range contains numeric data such as numbers and dates, use the COUNT function to count the non-empty cells.
In the following data table, I’ve calculated the total sales for November 2023.
I’ve skipped entering dates for off days in column A. Let’s count the total number of working days by referencing column A in the COUNT function.
=COUNT(A:A) // Counts cells with numeric values 
Subtract Blanks from the Range
| Function Used | Data Type | Syntax |
| COUNTBLANK | STATISTICAL | =COUNTBLANK(value1, [value2, …]) |
| ROWS | LOOKUP AND REFERENCE | =ROWS(range) |
The COUNTBLANK function in Google Sheets is used to count the empty cells in a range. However, if you subtract the count of blank cells from the entire range, you can count the total number of non-empty cells.
Take a look at this spreadsheet
The data table in range A1:E16 contains a list of activities conducted in grade five. However, there have been days where no activity was conducted.
We’ve been assigned to count the total number of active days. In cell B18, here’s the formula I entered:
=ROWS(D2:D16)-COUNTBLANK(D2:D16) //Subtracts the number of blank cells from the total range.
Set Condition to Count Cells If Not Empty
| Function Used | Data Type | Syntax |
| COUNTIF | STATISTICAL | =COUNTIF(range,criterion) |
| COUNTIFS | STATISTICAL | =COUNTIFS(criteria_range1), criterion1, [criteria_range2, ..], [criterion2, …]) |
While COUNTA is a great way to count cells that are not blank, it also takes hidden characters into account. To skip through hidden characters like spaces and line breaks, set conditions using COUNTIF and COUNTIFS.
COUNTIF and COUNTIFS are quite similar functions. The only differentiating factor between these functions is that COUNTIFS allows you to set multiple criteria.
We’ve covered these functions entirely in another article, check it out if it piques your interest!
The spreadsheet below holds the marksheet for 30 students.
Students who have passed are marked with a “P.” Students who failed the assessment were graded an “F” while absentee students weren’t marked at all.
If you look closely, however, there is a line break in the cells that appear empty. Let’s use this information to count the total number of students who appeared in the exam.
=COUNTIF(B2:B31,"<>"&CHAR(10)) //Counts all cells except ones with linebreaks.
Using SUMPRODUCT Function
| Function Used | Data Type | Syntax |
| SUMPRODUCT | INTEGER | =SUMPRODUCT(array1, [array2,...]) |
| LEN | INTEGER | =LEN(text) |
| TRIM | TEXT | =TRIM(text) |
You can set criteria to exclude counting cells with line breaks and spaces using COUNTIFS. However, if you’re dealing with a large data set, you will have to set multiple criteria for each of these null values.
To disregard these null characters without setting criteria, nest the TRIM and LEN functions inside SUMPRODUCT.
The TRIM function will first remove spaces, apostrophes, and linebreaks. Then, using the LEN function, we will determine if the characters in the cell are at least a character long.
Finally, the SUMPRODUCT function will calculate the total number of cells that have these characters.
Let’s calculate the total number of non-blank cells, excluding spaces, apostrophes, and linebreaks in column A.
In cell D3, here’s the formula I entered:
=SUMPRODUCT(LEN(TRIM(A:A))>0) //Counts all non-empty cells excluding spaces, apostrophes, and linebreaks.