Updated June 8, 2023
COUNTIF Not Blank in Excel
COUNTIF Not Blank in Excel is used for counting 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.
The syntax for COUNTIF is Not Blank in Excel
COUNTIF(Range, Criteria)
The syntax for COUNTIF Function includes 2 parameters which are as follows:
Range = The range we must select from where we will get the count.
Criteria = Criteria should be any exact word or number 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 sheet. Let’s understand the working of the COUNTIF Function in Excel through some examples 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. Counting the cell without blank cells for a large amount of data becomes very difficult. 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, column A has data from A2 to A7 and 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. Clicking on it opens the Insert Function box, displaying all the built-in functions Microsoft provides in a cell. Please search for the COUNTIF function by scrolling it up and down and then clicking 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. 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 the COUNTIF function, as shown in the below screenshot. And then click on Ok.
Once we click Ok, another box will appear for 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 cells containing values greater and lesser than any blank should be counted. And click on Ok.
The criteria can be anything, but we need to select a cell with any value greater or lesser than blank for a non-blank cell. For that, we used “<>” &””
If the criteria we have defined are correct, the Function Arguments box will show the box’s output on the bottom left side. It shows 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. Cell A6 is blank, so COUNTIF has neglected that cell and given the output of the remaining cell count, which has 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. Type COUNTIF and select it there, as shown in the screenshot below.
Pressing “=” (Equal sign) in any cell enables all the functions available in Excel. And even if we type selective words (Let’s say “Count”), as shown in the screenshot below, it will give all the possible functions available. 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 the value, “6”, but we selected 7 cells, including cell A6, which is blank. Here also, COUNTIF functions count the total cells that are not blank.
But if we put incorrect criteria, we may get an error message, which will explain the problem, as shown in the screenshot below. Here, we have removed “” (Inverted Commas) from the Criteria and got the error for testing.
To resolve an error, if unsure, click on the Help button, shown in the screenshot below, to access the Microsoft Excel Help window for guidance on correcting function arguments.
Note:
- “”(Inverted Comma) in the Excel function is used when any text or blank cell needs to be captured. Here <> is the text and “” together are used to capture the cell with any value but is not blank.
- Using “&” in a formula, we can add more criteria per our requirement.
Pros of Excel COUNTIF Not Blank in Excel
- Excel’s Countif Not Blank feature efficiently counts non-blank cells, saving time when working with large data sets.
- It gives an instant and exact result.
- The COUNTIF formula is fully automatic and 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 a 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 to get the exact result.
Recommended Articles
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, with practical illustrations and a downloadable Excel template. You can also go through our other suggested articles –
