How do you count if a cell is not blank on a sheet?
 Show
Excel COUNTIF Not Blank (Table of Contents)
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 ExcelCOUNTIF(Range, Criteria) 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 #1We 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 #2There 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. Note:
Pros of Excel COUNTIF Not Blank in Excel
Things to Remember
Recommended ArticlesThis 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 –
How do I count if a cell is not blank in Google Sheets?Count Cells If Not Blank Using COUNTA Function
Select an empty cell. Type =COUNTA( Highlight the range you wish to count. Press Enter.
How do you count a nonUse the COUNTA function to count only cells in a range that contain values. When you count cells, sometimes you want to ignore any blank cells because only cells with values are meaningful to you. For example, you want to count the total number of salespeople who made a sale (column D).
How do I count only filled cells?Ways to count cells in a range of data. Select the cell where you want the result to appear.. On the Formulas tab, click More Functions, point to Statistical, and then click one of the following functions: COUNTA: To count cells that are not empty. ... . Select the range of cells that you want, and then press RETURN.. Does Countif work with blank cells?As explained above, the COUNTIF function can be used for counting both, blank and non-blank cells. Now we will see how to use COUNTIF for counting blank cells.
|
