Excel COUNTIF Not Blank (Table of Contents)
- COUNTIF Not Blank in Excel
- Syntax for COUNTIF Not Blank in Excel
- How to use COUNTIF Not Blank in Excel?
COUNTIF Not Blank in Excel
COUNTIF Not Blank function is used for counting of any defined number/text range of any column without considering any blank cell. This becomes possible only by using the COUNTIF function, which follows the defined criteria to get the desired output.
Syntax for COUNTIF Not Blank in Excel
Syntax for COUNTIF Function includes 2 parameters which are as follows:
Range = It is the range which we need to select from where we will be getting the count.
Criteria = Criteria should be any exact word or number which we need to count.
The return value of COUNTIF in Excel is a positive number. The value can be zero or non-zero.
How to Use?
Using Excel Countif Not Blank is very easy. Here we will see How to use COUNTIF Function to find how many cells are not blank in Excel sheet. Let’s understand the working of COUNTIF Function in Excel by some examples given below.
COUNTIF Not Blank in Excel – Example #1
We have a small data of some random text and numbers in a column. And this column has a blank cell as well. Now for a large amount of data, it becomes very difficult to count the cell without blank cells. So, we will apply COUNTIF function with the combination of criteria which allows the formula to neglect the blank and give a total number of cells which has some value.
As we can see in the above screenshot, in column A, we have data starting from A2 to A7 and it has a blank cell in between at A6. Now to count the total cells but not blank, use COUNTIF. For that go to any cell where you want to see the output and click on fx (a tab to insert functions in excel), as shown below.
This fx tab is available just below the Menu bar. Once we click on it, we will get an Insert Function box, where it has all the inbuilt functions provided by Microsoft in a cell. Search for COUNTIF function by scrolling it up and down and then click Ok, as shown in below screenshot.
As we can see in the above Insert Function box, there is a tab named Or Select a category, which has all the categories for defined functions in it. From here, we can navigate to select All options as shown in the above screenshot or we can select Statistical category, where we will find COUNTIF function as shown in the below screenshot. And then click on Ok.
Once we click on Ok, another box will appear of Function Arguments, where we will need to define the Range and Criteria. Here, we have selected the range from A2 to A7 and Criteria as “<>”&””, which means cell containing value greater and lesser than any blank should be counted. And click on Ok.
The criteria can be anything, but for a non-blank cell, we need to select a cell with any value greater or lesser than blank. For that, we used “<>”&””
If the criteria which we have defined is correct then the Function Arguments box will itself show the output in the box as shown above in the bottom left side of the box. As it is showing the result of our defined range and criteria as 6.
Also in below screenshot, we got a count of cells which are not blank as 6. As cell A6 is blank, so COUNTIF has neglected that blank cell and gave the output of remaining cell count which is having some value (number or text).
COUNTIF Not Blank in Excel – Example #2
There is another method of using COUNTIF not blank, which counts all selected cells but not blank, by directly editing the cell. For this, go to the edit mode of any cell and press equal “=” sign which will enable all the inbuilt functions of excel. There type COUNTIF and select it, as shown in below screenshot.
Pressing “=” (Equal sign) in any cell, enables all the functions available in Excel. And even if we type selective words (Let say “Count”) as shown in below screenshot, it will give all the possible functions available to use. From there also, we can select the function as per our requirement.
As you can see in the above screenshot, COUNTIF function has range and criteria to be assigned, which was also there in the previous example. So we will assign the same range as A2 to A7 and criteria as “<>”&””, as shown below.
And press Enter key. We will get the count of cells which has value, which is “6” but we selected total 7 cells including the cell A6 which is blank. Here also, COUNTIF functions are counting the total cells which are not blank.
But if we put incorrect criteria, then we may get an error message, which will explain the occurred problem, as shown in below screenshot. Here, for testing, we have removed “ ” (Inverted Commas) from the Criteria and got the error.
If we do not know, how to resolve the error, then we can click on the Help button, shown in below screenshot, which will directly take us to Microsoft Excel Help window, which will guide to make function arguments right.
- “ “ (Inverted Comma) in excel function is used when there is any text or blank cell needs to be captured. Here <> is the text and “ ” together are used for capturing the cell which has any value but are not blank.
- With the use of “&” in a formula, we can add more criteria as per our requirement.
Pros of Excel COUNTIF Not Blank in Excel
- For large sets of data, where applying filter takes so much time, there using Excel Countif Not Blank feature is useful and time-saving for counting cells not blank.
- It gives an instant and exact result.
- COUNTIF formula is fully automatic, it is easy and instant to use.
- It is very helpful in accounting work.
Things to Remember
- Always check the data, if it is migrated output of the different source. There are some chances that the data may contain blank cells with hidden values. In that case, filter the blank cell and delete the cell values, to avoid incorrect output.
- Always unhide the sheet or column in order to get the exact result.
You can download this Excel COUNTIF Not Blank Template here- COUNTIF Not Blank Excel Template
This has been a guide to COUNTIF Not Blank in Excel. Here we discuss how to use COUNTIF Function to count Not Blank cells in excel along with practical illustrations and downloadable excel template. You can also go through our other suggested articles –