Md. Abdul Kader
In this tutorial, I am going to show you 8 useful methods to count cells that are not blank in excel. You can use these methods even in large datasets to find out the number of empty cells quickly. Throughout this tutorial, you will also learn some important excel tools and techniques which will be very useful in any excel related task.
Download Practice Workbook
You can download the practice workbook from here.
8 Useful Methods to Count Cells That are Not Blank in Excel
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 3 columns. Initially, we formatted all the cells containing dollar values in Accounting format. For all the datasets, we have 3 unique columns which are Items, January Sales, and February Sales. Although we may vary the number of columns later on if that is needed.
1. Using COUNTA Function
The COUNTA function in excel can count cells with certain criteria like containing logical values, texts, numbers, etc. Let us see how to use this function to count only cells that are not blank.
Steps:
- First, go to cell D10 and insert the following formula:
=COUNTA(B4:D9)
- Now, press Enter and this will calculate the total number of non-empty cells inside cell D10.
2. Applying COUNTIF Function
The COUNTIF function in excel counts the cells within a range that meet a specific single condition. We can use this function to count cells that are not blank.
Steps:
- To begin with, double-click on cell D10 and enter the below formula:
=COUNTIF(B4:D9, "<>"&"")
- Next, press the Enter key and you should get the number of cells with data.
3. Utilizing COUNTIFS Function
The COUNTIFS function in excel is similar to the previous function except that it can take multiple conditions. Follow the steps below to count cells that are not blank using this function.
Steps:
- To begin this method, double-click on cell D10 and insert the formula below:
=COUNTIFS(B4:D9,"<>")
- Next, press the Enter key and consequently, this will find the total cells count with data inside cell D10.
4. Count Non-Blank Cells Using COUNTBLANK Function
The COUNTBLANK function in excel is a type of statistical function. It actually counts the number of blank cells. But we can subtract this formula from the total cells to only count the cells that are blank.
Steps:
- To start this method, navigate to cell D10 and type in the following formula:
=ROWS(B4:D9)*COLUMNS(B4:D9)-COUNTBLANK(B4:D9)
- After that, press the Enter key or click on any blank cell.
- Immediately, this will give you the filled cell count inside cell D10 as 14.
5. Using SUMPRODUCT Function
The SUMPRODUCT function in excel can count cells that are not blank similar to the previous functions but it gives us more flexibility. Let us see how to use this.
Steps:
- As previously, insert the below formula inside cell D10:
=SUMPRODUCT((B4:D9<>"")*1)
- Finally, press the Enter key and we should get the result as 14.
6. Applying LEN Function to Count Non-Blank Cells
The LEN function in excel basically measures the length of a text string. We will use this function with the SUMPRODUCT function to count cells that are not blank.
Steps:
- To begin with, the process, navigate to cell D10 and type in the formula below:
=SUMPRODUCT(--(LEN(B4:D9)>0))
- Then, press Enter and this will count the total number of cells with data value inside cell D10.
7. Utilizing Find & Select Feature
This feature in excel is very useful to search for cells with certain criteria and also replace their content. So it can also count cells that are not blank using proper criteria. Follow the steps below to apply this feature.
Steps:
- For this method, select all the cells from B4 to D9.
- Next, go to the Home tab and click on Find & Select under Editing.
- Here, click on Find.
- Now, in the new Find and Replace window, type * in the Find what field and click Find All.
- As a result, you should see the total non-empty cells count at the bottom of this window.
8. Counting From Status Bar
This method is very quick and simple as we shall see below. This can be very useful if we want to count cells that are not blank in multiple worksheets.
Steps:
- First of all, select all the cells from B4 to D9.
- Consequently, you should get the total number of filled cells at the bottom of the excel window.
How to Count Blank Cells in Excel
Very often we might need to count the cells that are blank within a dataset. Below are the steps to do this.
Steps:
- To begin with, double-click on cell D10 and insert the formula below:
=COUNTBLANK(B4:D9)
- Next, press the Enter key and you should immediately get the total number of blank cells.
Conclusion
I hope that you were able to apply the methods that I showed in this tutorial on how to count cells that are not blank in excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
