0 ITEMS
Can You Count Blank Cells In Excel?
You can use Excel to pretty much count almost everything – numbers, texts, dates, times, errors, and even spaces in your financial model. The one question left is, can you use it to count blank cells in Excel? When you are importing a large amount of data into a financial model, it is important to check for any missing data for the model to be robust. If your business relies on timely inputs of data, a single unaccounted input in the financial model could lead to costly business decisions. Counting blank cells in Excel allows you to identify missing, unaccounted, and misplaced data.
Using the COUNTBLANK function in Excel allows you, judging by the function’s name, to count blank cells in Excel. Is it that simple? This tutorial will walk you through the formula of the COUNTBLANK in Excel and how it counts empty cells in Excel.
What Does it Mean to Count Blank Cells in Excel?
The COUNTBLANK function in Excel, just like other COUNT Blank functions in Excel, is under the Statistical functions and is used to count empty cells in Excel. Others describe it as a function to count blank cells in Excel, either way, it is correct.
The Formula for Counting Empty Cells in Excel
The Syntax for the COUNTBLANK function in Excel:
| =COUNTBLANK(range) |
Where:
Range is a required argument and represents the range of cells where the blank or empty cells are counted.
Before we work with COUNTBLANK function in Excel examples, let us define first an empty or blank cell.
What is an Empty or Blank Cell?
For the COUNTBLANK Excel function to properly count blank cells in Excel, a cell must not contain any of the following:
- A cell with a zero value
- A cell containing a space or formula that results in an empty string (“ ‘”)
- Cells containing numbers, dates, time, text, and errors values
The Range in the COUNTBLANK Excel formula must be absolutely empty or blank. In other words, there should be completely nothing.
Examples on How to Count Empty Cells in Excel
To master the COUNTBLANK Excel function, let us use a simple inventory table tracking the business’ products at each store. Let’s use the COUNTBLANK Excel function to count blank cells in Excel representing any missing data from our table.
Count Empty Cells In Excel With Columns As Cell Range (Vertical Range)
Based on store by store (Columns B, C, and D) category, the COUNTBLANK Excel formula =COUNTBLANK(B2:B11) results in a total count of 3 blank cells under Store A.
Let’s apply the same COUNTBLANK Excel formula to Stores B and C.
The COUNTBLANK Excel formula =COUNTBLANK(C2:C11) results in a total number of 1 blank cell under Store B. Take note, that Store B contains one cell (C8) with zero value. The COUNTBLANK Excel function ignores the zero since this cell is not technically blank.
Lastly, applying the COUNTBLANK Excel function to Store C to count blank cells in Excel, the COUNTBLANK Excel formula =COUNTBLANK(D2:D11) results in a total count of only 1.
Just by looking at the table alone, you can tell that Store C has 2 blank cells; D3 and D8. So, why does the COUNTBLANK Excel function count only 1 blank cell?
Recall the meaning of an empty or blank cell, a cell containing a space or formula that results in an empty string (“ ‘”) is a non-blank cell and will not be counted.
Cell D3 contains an empty string (” ‘”) and is rightly considered as a non-blank cell and so is excluded from the count.
Count Empty Cells in Excel with Rows as Cell Range (Horizontal Range)
Based on Product Names (Rows 2 to 11) count, the COUNTBLANK Excel formula =COUNTBLANK(B2:D2) results in a total number of zero blank cells for Brand 1.
The below table reflects the total count of empty cells in Excel per row.
How to Count Empty Cells in Excel for Both Rows and Columns (Mixed Range)
You can also count blank cells in Excel for the entire table. The COUNTBLANK Excel formula =COUNTBLANK(B2:D11) results in a total count of 5 blank cells.
Count Empty Cells in Excel for Multiple Ranges
As mentioned, the Range in the COUNTBLANK Excel formula does not work for non-contiguous (non-adjacent) and multiple ranges of cells. For example, assume you want to count blank cells in Excel under Store A and Store C only thereby excluding Store B.
If you try to use COUNTBLANK Excel formula =COUNTBLANK(B2:B11,D2:D11) and once you hit enter, Excel will give you an error message.
But what if you need to count blank cells in Excel for these specific columns? Is there a workaround for the COUNTBLANK Excel formula?
Yes, you can simply insert another COUNTBLANK Excel formula. You can add the results of these 2 COUNTBLANK Excel formulas by simply inserting the plus sign (+).
Difference Between COUNTBLANK Excel Function and COUNTA Function
Both COUNTBLANK Excel Function and COUNTA Function are part of the COUNT functions in Excel. The key difference between the two is what it is designed to count, it is safe to say that COUNTBLANK Excel Function and COUNTA Function are total opposites.
COUNTBLANK Excel Function
- Designed to count blank or empty cells. Cells that contain completely nothing.
COUNTA Function
- Designed to count cells that are non-blank or non-empty. Cells that contain any type of information such as numbers, dates, time, text, error values, and empty strings.
