Excel COUNTIF Not Blank (Table of Contents)
- COUNTIF Not Blank in Excel
- The 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 = The range 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 the Excel sheet. Let’s understand the working of the COUNTIF Function in Excel by some examples given below.
COUNTIF Not Blank in Excel – Example #1
We have small data of some random text and numbers in a column. And this column has a blank cell as well. It becomes very difficult to count the cell without blank cells for a large amount of data. So, we will apply the COUNTIF function with the combination of criteria that allow the formula to neglect the blank and give a total number of cells with 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 the COUNTIF function by scrolling it up and down and then click Ok, as shown in the 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 we have defined are correct, then the Function Arguments box will show the box’s output as shown above on the bottom left side of the box. As it is showing the result of our defined range and criteria as 6.
Also, in the below screenshot, we got a count of cells that are not blank as 6. As cell A6 is blank, so COUNTIF has neglected that blank cell and gave the output of the 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 the equal “=” sign, enabling all the inbuilt functions of excel. There type COUNTIF and select it, as shown in the 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 the 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, the COUNTIF function has a 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 with value, which is “6”, but we selected 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 the below screenshot. Here, we have removed “ ” (Inverted Commas) from the Criteria and got the error for testing.
If we do not know how to resolve the error, we can click on the Help button, shown in the below screenshot, which will directly take us to the Microsoft Excel Help window, guiding us to make function arguments right.
- “ “ (Inverted Comma) in excel function is used when there is any text or blank cell that needs to be captured. Here <> is the text and “ ” together are used to capture the cell with any value but is 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, they’re 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.
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 a downloadable excel template. You can also go through our other suggested articles –