What function counts the number of cells that are not blank within a specified range of cells?
|
Sometimes you have a set of data in Excel that contains some blank cells. You can use the COUNTA function to create a formula that will only count the cells that are not blank. Show
COUNTA to Count Cells that are Not BlankLet’s say you want to count cells that contain data, for example, the numbers of students in a data range. And you would like to ignore any cell that is blank. The way to do go about doing this is to use the COUNTA function. The COUNTA function allows you to count cells containing any type of information. It can be error values and empty text (“”). If the range contains a formula that returns an empty string, this function counts that value. So the COUNTA function counts all cells that contain any type of “value” and does not count empty cells. To count the total number of students in a range A1:A30 and add the result in A31 cell then select A31 cell and type : =COUNTA(A1:A30)  And press Enter. And the answer will be 6. Count Cells Containing Text ValuesIn case you need to count only cells containing any TEXT VALUES, so excluding cells dates and numbers, treating as blank cells and not including in the count value you can use this formula : =COUNTIF (range, “*”) The above formula counts only cells containing text values, so that means that cells with dates and numbers will not be included in the count but will be treated as blank cells. In the generic form of the formula range is a range of cells, and “*” is a wildcard matching any number of text characters. The formula for counting all non-blank cells in a range is (as COUNTA function above) : =COUNTIF (range,”<>”&””) This formula will correctly count all type of values – text, dates, and numbers, as you can see in the screenshot below. Count Cells Containing Specific TextTo count the number of cells that contain specific text in a range you can use : =COUNTIF(range,”*text*”) So the result in cell F3, shows the counting value in range B2:B10 searching for criteria “i”. Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. Did this post not answer your question? Get a solution from connecting with the expert.Another blog reader asked this question today on Excelchat: I want to apply a formula to filtered cells only (i.e. what the subtotal function does, but with formulas that are not under the subtotal scope) An Excelchat Expert solved this problem in 26 mins! I have a large spread sheet I need to filter. I can't filter because there are to many blank rows and random blank cells. Ideally I want a formula to delete every row that doesn't have something in a particular column. If not I need a formula to delete just the blank rows but not all the blank cells. Every time i delete the blank cells it deletes everything and i can't filter to select the blank rows because there are two many empty rows and headings. An Excelchat Expert solved this problem in 27 mins! how to make a formula not show up a dollar value when the cells in the formula are blank? An Excelchat Expert solved this problem in 17 mins! 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 you count if a cell is not blank?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.
Which function counts the number of blank cells within a range?Use the COUNTBLANK function, one of the Statistical functions, to count the number of empty cells in a range of cells.
What function counts the number of cells in a range based on specified criteria?The COUNTIF Function[1] will count the number of cells that meet a specific criterion.
|
