COUNTIF Not Blank Function

Note: Alternatively, the COUNTA functionCOUNTA FunctionThe COUNTA function is an inbuilt statistical excel function that counts the number of non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but, cell A2 is empty. The formula “=COUNTA(A1,A2,A3)” returns 2. read more can be used to count the non-blank cells.

How to Use COUNTIF Non-Blank Function?

The steps to count non-empty cells within text values are listed as follows:

  • In Excel, enter the following data containing both, the data cells and the empty cells. Enter the following formula to count the data cells. “=COUNTIF(range,”<>”&””)”In the range argument, type B2:B30. Alternatively, select the range B2:B30 in the formula, as shown in the following image. Press the “Enter” key. The number of non-blank cells in the range B2:B30 appear in cell C2. The output is 26, as shown in the succeeding image. This implies that there are 26 cells in the given range that contain a data value. This data can be a number, text, or any other value.

“=COUNTIF(range,”<>”&””)”In the range argument, type B2:B30. Alternatively, select the range B2:B30 in the formula, as shown in the following image.

This implies that there are 26 cells in the given range that contain a data value. This data can be a number, text, or any other value.

  • Step 1: In Excel, enter the data as shown in the following image.

  • Step 2: Select the range within which data needs to be checked for non-blank values. Enter the formula shown in the succeeding image.

  • Step 3: Press the “Enter” key. The number of non-blank cells in the range B2:B21 appear in cell C2. The output is 15, as shown in the succeeding image.

Hence, the COUNTIF not blank formula works with text values.

The steps to count non-empty cells, when the data consists of dates, are listed as follows:

  • Step 1: In Excel, enter the data as shown in the following image. Select the range whose data needs to be checked for non-blank values. Enter the following formula.

“=COUNTIF(B2:B21,”<>”&””)”

  • Step 2: Press the “Enter” key. The number of non-blank cells in the range B2:B21 appear in cell C2. The output is 14, as shown in the succeeding image.

Hence, the COUNTIF not blank formula works with data that consists of date values.

The Characteristics of COUNTIF Not Blank Function

  • It is case insensitive, implying that the output remains the same irrespective of whether the formula is entered in uppercase or lowercase.It works for data that consists of numbers, text, and date values.It works with greater than (>) and less than (<) operators.It is difficult to use the formula with long strings.The criteria (condition) must be specified within a pair of inverted commas to avoid errors.

Frequently Asked Questions

The universal formula for counting blanks is stated as follows: “COUNTIF(range,””)” This formula works with all types of data values. Note: Alternatively, the COUNTBLANK function can be used to count blank cells.

The formula for counting the duplicate value is given as follows: “COUNTIF(range,“duplicate value”)” The “range” represents the range within which the duplicate values are to be counted. The “duplicate value” is the exact data value that is to be counted.For example, to count the number of times the text “fruits” appears in the range A2:A10, we use “=COUNTIF(A2:A10,“fruits”).”

Key Takeaways

  • The COUNTIF not blank function counts the non-blank cells within a given range.The generic formula of the COUNTIF not blank function is stated as–“COUNTIF (range,“<>”&””).”The criteria (condition) must be specified within a pair of inverted commas to avoid errors.The COUNTIF functionCOUNTIF FunctionThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump”
  • read more works for data that consists of numbers, text, and date values.The COUNTIF formula gives the same output irrespective of whether the formula is entered in uppercase or lowercase.

This has been a guide to Excel COUNTIF not blank. Here we discuss how to use the COUNTIF function to count non-blank cells along with practical examples and a downloadable Excel template. You may learn more about Excel from the following articles –

  • Not Equal in VBACOUNTIF with Multiple CriteriaVLOOKUP ErrorsUse Not Equal to in ExcelXML in Excel