When you’re analyzing data in a spreadsheet, counting empty or blank cells could help you focus on specific areas. This is why functions like COUNTBLANK, COUNTIF, COUNTIFS, and SUMPRODUCT are so important in Google Sheets.
A word of warning, however. If you have a cell that contains an empty text string (“”) or has a formula that returns a similar result, this cell would be blank, but it wouldn’t technically be empty. If you want to know the number of truly empty cells, you’ll need to use a combination of the SUM, ROWS, COLUMNS, and COUNTIF functions.
You can try the COUNTBLANK function to count the number of blank cells in a Google Sheets spreadsheet. This is the quickest way to find the number of blank, but not empty, cells.
Cells that contain numbers or text won’t be counted, including cells with the number zero. As we’ve mentioned, however, if a cell looks empty but contains an empty text string (“”), this will be counted.
To use it, open your Google Sheets spreadsheet. Click on an empty cell and type . Replace with your cell range.
For instance, if you wanted to count the number of blank cells between columns A and C, you’d type .
In the example above, cells from A3 to H24 are used within the range. This range contains four blank cells (B4, C4, D4, and E4), which is the same figure COUNTBLANK reports in cell A1.
Using COUNTIF and COUNTIFS
While COUNTBLANK returns the number of blank cells, you can also use COUNTIF or COUNTIFS to achieve the same result.
COUNTIF counts the number of cells that meet the criteria you define within the formula itself. Because you want to count empty cells, you can use a blank text string as your criteria.
RELATED:How to Use the COUNTIF Formula in Microsoft Excel
To use COUNTIF, open your Google Sheets spreadsheet and click on a blank cell. Type , replacing with your chosen cell range.
The example above has three blank cells (B4, C4, and D4) within the range A3 to H24, with the COUNTIF function in cell A1 returning the same number of blank cells.
The COUNTIFS function can be used as an alternative to COUNTIF. Use , replacing with your selected cell range.
In the example above, four blank cells within the A3 to H24 cell range were found.
The SUMPRODUCT function offers a slightly more complex route to counting the number of blank cells. It counts the number of cells matching certain criteria which, in this case, would be an empty text string (“”).
To use SUMPRODUCT, open your Google Sheets spreadsheet and click on an empty cell. Type , replacing with your chosen cell range.
The example above shows that within the A2 to H24 cell range, two blank cells (B4 and C4) were found.
Counting Empty Cells
All of the functions listed above count cells that are blank but which aren’t technically empty. If a function returns a null or empty result, or if you have an empty text string (“”) in a cell, then those cells are counted as blank.
A workaround to this problem is to use COUNTIF to count the number of cells with a numerical value, then to use a second COUNTIF formula to count the number of cells containing text or empty text strings.
You can then add the results from these calculations and subtract them from the number of cells in your data range. You’ll need to know the number of cells in your range first. To find that out, you can use the ROWS and COLUMNS functions.
To start, open your Google Sheets spreadsheet, click on an empty cell and type , replacing the value with your cell range.
In a second empty cell, type to count the number of cells with a numerical value. Once again, replace with the appropriate cell range for your data.
To search for blank cells or cells containing text, type in a third empty cell. Replace as required.
You can then use SUM to add up your two COUNTIF values, subtracting that figure from the number of cells in your range calculated using the ROWS and COLUMNS functions.
In our example, the total number of cells can be found in cell B8, the number of cells with a numerical value in B9, and the number of cells that contain text or an empty text string in B10.
Replacing these cell values with your own, you could use to determine the number of truly empty cells in your range.
As the example above demonstrates, in a range of 20 cells (A2 to E5), 19 cells were found to have either a number, text, or empty text string. Only one cell, E4, was completely empty.
How to Count If Not Blank in Google Sheets
To count if not blank in Google Sheets is useful if you want to count the cells that just have data in it (not blank).
The technique is commonly used when you have to count a specific cell range, especially if you have a large number of cells and you want an accurate count of the cells which are not blank.
For instance, you are running a small supermarket with 200 different items. You listed all the names of the items in Google Sheets. Using the techniques shared below, you can precisely obtain the right amount of stock ignoring cells that are blank and useless.
Ready to get started? Let’s dive right in! 😊
How To Count If Not Blank in Google Sheets: 3 Ways
1. Using COUNTA Function.
For the first approach when counting if not blank cells, we will be using the (Count All) Function.
The syntax or in other words the way we write the function is as follows:=COUNTA(value1, [value2, ...])
Let’s break this COUNTA function down:
- = denotes the start of every formula written in Google Sheets.
- is our function of which we need to provide its corresponding .
- we have to select the cell range that we want to count. This value is required to be given and the function will not work without it.
- , and subsequent attributes ‘‘ are additional cells to be included in the count. The maximum number of entries allowed is 30.
It’s a good idea to have some understanding of how to use the COUNTA function in Google Sheets before delving into using to count if not blank cells.
Now the big question in your mind at this point is, “what’s the use of learning this function?”
Well, when you are really sure that all your cells are blank, you can use the function to count the cells which are not blank.
Let’s take an example.
In the image below, cells A4:A6, A9, A11:A12, and A16 are empty. Using our finished function which is it will result in an answer of 8 which is correct as there are only eight cells which are not blank as shown below:
When you’re unsure of cells being blank, such as if you may have mistakenly entered an apostrophe or space in any of the cells, then will count that too.
This is where is not ideal and the other solutions shared in this guide help circumvent.
In such a case, it’s best we rely on the COUNTIF function in Google Sheets which we will show you how below. 🙂
2. Using COUNTIF Function.
As aforementioned, using the function is perhaps the best solution to counting cells that are not blank in Google Sheets.
It’s not uncommon to find ourselves committing little errors when typing or inputting data in our spreadsheets. There might be cases where we hit the space bar on a cell, then pressed enter without knowing. Or maybe thought to add a text, added an apostrophe, but you left it that way. In such instances, using the function will definitely accurately provide us the right count for cells that are not blank.
I’ll give you an example below.
When you typed in an apostrophe and left it that way, it won’t actually register on Google Sheets, but it’s there. In other words, it’s “as if” empty.
As you can see in the example above, I typed in a space on cell A4 and an apostrophe on cell A11. Instead of having an outcome of 8, it gave me a result of 10. Meaning, the function included the space and the apostrophe in the count.
This is a clear picture of a situation where the function fares well.
We can use the formula as shown below:=COUNTIF(A2:A15,">0"&"*")
Let’s break this function down to understand how it works:
- = denotes the start of every formula written in Google Sheets.
- is our function. We need to add other attributes for it to work smoothly.
- is the group of cells that the function is to search. For our example, we will be using A2:A15 as the range which contains the cells we want to count.
- is the condition where each cell in the range is to be tested whether it is to be true or false. For this we provide the following:
- is a comparison operator which means “greater than 0”. In this case, we want to say that the criterion should be that anything that is lesser than 0 should be avoided. You probably learned this from the previous COUNTIF post.
- is called ampersand and it merges the comparison operator and the asterisk.
- is called a wildcard character and represents or take the place of any number of characters. This is useful as we need to avoid counting unnecessary characters which are not blank such as, for example, apostrophes, full stops, commas, or even spaces.
After applying the function, we manage to obtain the correct count of cells which is 8 ignoring the spaces and unwanted characters which appear as blank cells.
⚠️ Now a few notes about the difference between COUNT and COUNTA
The function is commonly used when counting a range of cells that includes numbers and dates. It doesn’t count blank cells.
On the other hand, function counts numbers, texts, dates, characters and even spaces.
⚠️ A few more notes when using these formulas
- The function is used when the data is perfect, meaning, with no unnecessary characters like apostrophe and space.
- When using the function, make sure to supply the necessary attributes such as the comparison operator, the ampersand, and the asterisk. Provided so will allow your function to filter out cells that are not blank but appear as blank in your spreadsheet.
3. Using SUMPRODUCT Function.
The third solution to use is to utilize the function and it works great at counting non-empty cells.
To make use of it we will need to provide it two other extra functions too which are the and functions. Doing so will allow the function to ignore cells that may have null string or spaces as well as unwanted characters such as apostrophe being counted.
We can use the formula as shown below:=SUMPRODUCT(LEN(TRIM(A2:A15))>0)
Again let’s break this down:
- = required to start the function.
- SUMPRODUCT() works by calculating the sum of products of the entries. In this case, since we only provide one entry which is A2:A15 and not separated by a comma.
- LEN() is responsible to make sure each cell is inspected to see if there is at least one character or number in it. In other words, the length of the characters will have to be >0 (greater than 0). If not, it will be avoided.
- TRIM() makes sure that any spaces are ignored.
- >0 as mentioned, the length of characters will have to be greater than 0 in order to be considered. If not it is not counted and is considered blank.
When combined and implemented, the function will also provide a count of 8 just like did.
However, even though the function avoids hidden characters and spaces entered in the data from being counted, the one caveat is that it does count numbers and characters such as brackets, apostrophes, commas, etc. which are included as part of the data as well.
In this case, you will need to resort back to using the function if you really want to avoid such characters from being counted.
How to Count If Not Blank in Google Sheets Using COUNTIF
You can follow along in this section to write your own function to count non-empty cells in Google Sheets. Since is the more robust out of the three solutions discussed above, we will be using it instead.
We begin as follows:
- First, click on any cell and make it active. For this guide, I will be choosing D5.
- Next, we enter =COUNTIF followed by an opening parenthesis ‘(‘ to begin our function.
- Now for the first attribute which is our range, we will select the enter A2:A15, as this is the cell range that we want to count.
- Next, after entering the comma ‘,‘ we provide the criterion to evaluate the range we had already provided. For this criterion, we enclose in a quote-unquote symbol (“”), we will write greater than zero, or “>0“. Remember this is to count cells that are greater than 0 as we want to say that the cells which are lesser than 0 should be avoided.
- After this, we will add an ampersand ‘&‘ to connect our first criterion with our second criterion. You do not have to enter a comma ‘,‘ here.
- Now we provide the second criterion for which we type in the asterisk ‘*‘. This is to ignore characters that we may accidentally input in the cell range which appear as blank cells.
- Finally, enter the closing parenthesis ‘)‘ and hit the Enter key to obtain the final result.
That’s it. Well done! 👏🏆
You may make a copy of the spreadsheet using the link I have attached below:
Have a feel on how to work with this formula. Try it out for yourself.
That’s pretty much it. You now know how to count if not blank in Google Sheets. Try experimenting it together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier. 🙂
Get emails from us about Google Sheets.
Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.
How to Count Blank or Empty Cells in Google Sheets
Google Sheets supports several functions that count the number of cells in a selected range containing a specific type of data. The COUNTBLANK function calculates the number of cells in a selected range with null values.
Instructions in this article apply to the web and mobile versions of Google Sheets.
COUNTBLANK Function's Syntax and Arguments
A function's syntax refers to the layout of the function, including its name, brackets, comma separators, and arguments. The syntax for the COUNTBLANK function is as follows:=COUNTBLANK(range)
The range argument identifies one or more cells with or without data included in the count. The range may contain:
- Individual cell references to the location of the data in the worksheet.
- A range of cell references.
- A named range.
For example, to calculate the number of blank cells in A2 through A10, you would enter:=COUNTBLANK(A2:A10)
The range must be a contiguous group of cells. COUNTBLANK doesn't permit multiple ranges. However, several instances of the function can be used in a single formula to find the number of blank or empty cells in two or more non-contiguous ranges.
The COUNTBLANK function includes both cells with no data and cells with formulas that have a blank or null value in its count.
How to Use the COUNTBLANK Function
Unlike Excel, Google Sheets doesn't have dialog boxes for entering a function's arguments. Instead, it has an auto-suggest box that appears as the name of the function is typed.
To count the number of blank cells in a range with the COUNTBLANK function:
Select any cell to make it the active cell.
Type =COUNTBLANK and press the Enter key.
Alternatively, select =COUNTBLANK from the auto-suggest box as it appears while you're typing.
Select a range of cells to include that range in the function's argument.
To highlight multiple cells simultaneously, hold the Shift key as you make your selection.
Press Enter to complete the function. The number of blank cells within the range appears in the cell where you entered the COUNTBLANK function.
Thanks for letting us know!
Count Cells IF NOT Blank (Non-Empty cells) in Google Sheets
Google Sheets has some great formulas that allow you to quickly get information about a dataset.
One of the common things a lot of people need to do often is to count cells that are not blank in a dataset in Google Sheets (i.e., count non-empty cells).
While it can be done manually if you have a small dataset, for large ones, it’s best to leave this to the awesome COUNT formulas in Google Sheets.
And again, there are multiple ways to skin this cat.
In this tutorial, I will show you two really simple formulas to count cells if not blank in Google Sheets.
Count Cells If Not Blank Using COUNTA Function
Google Sheets has a COUNTA function that counts all the non-empty cells in a dataset (i.e., all the cells that have any text string or number in it).
Suppose you a dataset as shown below and you want to count the non-empty cells in it.
You can use the below COUNTA formula to count all the non-empty cells in the dataset:=COUNTA(A2:A13)
While COUNTA formula gives the right result, in this case, it may give you a wrong result in case there is a:
- Space character
- Null/empty string (=””).
- Apostrophe (‘)
An empty string may be a result of a formula and a lot of people use apostrophe while entering numbers (to show these numbers as text)
In all the above cases, it may look like the cells are empty, but the COUNTA function would still consider these as non-blank and count these in the result.
Below is an example where I have an empty string (=””) in cell A4 and an apostrophe in cell A8. You can see that the result of the COUNTA formula is 11 (although only nine cells are filled with names – or at least that’s what appears to a naked eye).
In case you’re sure your data doesn’t have empty strings or apostrophe, you can use the above COUNTA formula, but in case there is a possibility of it, it’s better to use a combination of formulas to get this done (covered in the next section).
Count Non-Empty Cells Using SUMPRODUCT Function
It’s wonderful how easily you can solve complex problems with a simple combination of formulas in Google Sheets.
When it comes to counting non-empty cells in a dataset, there could be cells that have null string or spaces or apostrophe.
While you can’t completely rely on the COUNTA formula, here is a simple SUMPRODUCT formula that will give you the right result in all the scenarios:=SUMPRODUCT(LEN(TRIM(A2:A13))>0)
The above formula checks whether the cell has at least one character/number in it or not. This is checked by the LEN function.
If the length of the characters in the cell is more than 0, it’s counted, else it’s not counted.
This takes care of two scenarios – null strings and apostrophe.
And the TRIM part of the function makes sure that if there are space characters in the cells, these are ignored as well.
In case you have error values in the cells, then this formula will give you an error.
[Quick TIP] Get Count Value in the Task Bar
If you quickly want to quickly get the count of non-blank cells, you can get that from the taskbar.
All you need to do is select the cells in which you want to get the count of cells that are not blank and see the COUNT value in the taskbar (in the bottom-right part of the Google Sheets document).
In case there are numbers in your dataset, by default the taskbar will show you the SUM and not the COUNT. In that case, simply click on it and it will show you the COUNT (along with other data such as Average or Max/Min).
Note that it will include all the cells that have anything in it – be it an empty string (=””), apostrophe (‘) or space character.
These are the three ways you can use to quickly get the count of cells if not blank in Google Sheets.
You may also like the following Google Sheets tips and tutorials:
Sheets filled google cells count
How to Count Non-Empty Cells in Google Sheets
When using Google Sheets, there may be times where you have cells with no data. In these cases, you may want to count the number of cells that are not blank. This is possible by using Google Sheet COUNT functions!
COUNTA is a function for counting cells that are not empty. Sometimes, however, the COUNTA function doesn't catch hidden special characters, so we can use SUMPRODUCT or COUNTIF to resolve this issue.
If you do not know how to use these functions, this tutorial is for you.
In Google Sheets, the COUNTA function counts all the number of cells within a range that have values or a text string in it. It ignores cells that are empty while completing the count.
As an example, here is data that has blank cells within a range:
To count the number of cells that are not blank, just follow these steps.
- Select a blank cell and type the =COUNTA function including the range of cells that you want to count. For example, we used =COUNTA(A2:A11).
- Just hit enter, and the COUNTA function will automatically count the cells that are not blank.
- You now have the total number of cells that have values in it!
COUNTA function is very useful in counting cells that are blank or have no data in it. However, there may be times that COUNTA counts cells that seem empty. This is because, technically, those cells are not blank but just have a white space appearing on it. It happens, for instance, when you use conditional formatting that makes it appear as blank or sneaking a function =”” in it!
To avoid this from happening, use SUMPRODUCT instead by following these steps:
- Click on an empty cell and input the function =SUMPRODUCT(--(LEN(range)>0)) to count the cells that do not appear empty. In the process, the LEN function returns a value that is greater than zero while counting the number of characters that appear in the sheet.
- Just click Enter and the number of the cells that have values in it will automatically appear.
If you have read the previous tutorials, Google Sheets hide some special characters from view because of their special functions: this include the apostrophe, that it uses to indicate that the number characters should be interpreted as a string (we used this to input phone numbers to Google Sheets). To solve this, we will learn how to use COUNTIF to count cells that are not blank and do not have special characters hidden.
As an example, let us look at this table:
How many cells have entries in the table? There are nine (9) entries in that table. However, if you apply COUNTA function to count the entries, the result would be thirteen (13):
The reason is that there are stray apostrophes and spaces in that table, and COUNTA counted them in. To exclude them from the count, we apply the COUNTIF function with a special attribute:
What happened? The “>0”&”*” serves as the condition to filter out cells that contain hidden characters, thus only the ones with letters and numbers are counted.
Want to know more about Google Sheets?
Do you have more questions in mind about using Google Sheet? Check out our other spreadsheet resources and learn more functions and tricks that you can use when working!
How to Count Cells If Not Blank in Google Sheets
Prefer watching a video tutorial? Watch our video on how to count cells if not blank in Google Sheets below:
Sometimes you may have a dataset where some of the cells or rows are blank. In such a case, if you want to count cells that are not blank, then you need to rely on Google Sheets count functions.
In this tutorial, I will show you how to count cells if not blank in Google Sheets.
Count Cells If Not Blank Using COUNTA Function
COUNTA function in Google Sheets counts all the cells that have any value or text string in it, and ignores the blank cells.
For example, if you have a dataset as shown below and you want to count cells that are not blank, you can use the COUNTA function.
While the COUNTA function works well in most of the cases, it fails to give the right result when there is an empty string in the cell, or there is an apostrophe.
An empty string (“”) can be a result of a formula while apostrophe is often used by many people during data entry.
The empty string and apostrophe make the cell look blank, but are not ignored by the COUNTA function. So if you have a cell that has these, it will be counted by COUNTA function (as shown below):
Note that the cell A3 has an empty string (“”) and cell A7 has an apostrophe (‘). While these look empty, these cells are counted by the COUNTA function.
Hence, the result is 12, while it should be 10.
Count Cells If Not Blank Using COUNTIF function
Countif function in Google Sheets can be used to count the number of cells when a specified condition is met
To count all the cells that are not blank, you can use the following function:=COUNTIF($A$1:$A$13,"?*")+COUNT($A$1:$A$13)
Note that this formula ignores blank cells that contain an apostrophe or empty string.
This formula would also ignore Logical Values (TRUE/FALSE) and Errors.
You can also use the below SUMPRODUCT formula for counting non-blank cells in Google Sheets:=SUMPRODUCT(LEN(A1:A13)>0))
Spreadsheet ExpertatProductivity Spot|Website|+ posts
Google Sheets and Microsoft Excel Expert.
- Roper staffing application
- Matrix 80 lower
- Optimus 5b speakers
- Cummins exhaust flange
- Keyboard brush kit
- Sawgrass sg500 software
- Mosaic house numbers
- K praise radio
A choked groan interrupted my vision. Before sinking into the seat, her mouth still opened, releasing a small part of the scream that was torn from her tormented. Bladder.