Home ➜ Excel Formulas ➜ Count Blank (Empty) Cells using COUNTIF If you want to count cells that are blank in Excel, you can use the COUNTBLANK function
which is specifically designed to count cells that are empty (without any value in them). In the COUNTBLANK function, you just need to refer to the range from where you want to count the blank cells. In the following example, you have a few values in the range A1:A10, but a few of the cells are empty. And now, you need to count the cells those cells with no values in them. You can use the following steps to write this formula:
 =COUNTBLANK(A1:A10) Once you hit enter it returns the count of the cells that are blank in the specified range. You can also use COUNTIF and create a condition to count blank cells. By using the same example, you can follow the below steps to write this formula:
And the moment, you hit enter it returns the count for blank cells. You can also use a formula like the following with the “=”&”” criteria. When you use it, it also tells Excel to count only cells with no value in them. 🔙 Excel Formulas More FormulaWhen 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. COUNTA MethodIn 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.
SUMPRODUCT MethodCOUNTA 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:
COUNTIF MethodIf 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: =COUNTIF(B3:E7,">0"&"*") 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! Which function counts the number of cells that are not blank?Use the COUNTA function to count only cells in a range that contain values.
Which of the following functions counts the number of cells that are not blank within a specified range of cells count Countblank Counta sum?Use the COUNTBLANK function, one of the Statistical functions, to count the number of empty cells in a range of cells.
What function counts the number of cells in Excel?The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.
Does count function count blank cells?The COUNTBLANK Function[1] is categorized under Excel STATISTICAL functions. COUNTBLANK will count the number of empty cells in a given range of cells. In financial analysis, the function can be useful in highlighting or counting empty cells in a given range.
|
