Countif Not Blank in Excel

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Vandana Kataria

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

COUNTIF Not Blank Function

The COUNTIF not blank function counts non-blank cells within a range. The universal formula is “COUNTIF(range,"<>"&"")” or “COUNTIF(range,"<>")”. This formula works with numbers, text, and date values. It also works with the logical operators like “<,” “>,” “=,” and so on.

Note: Alternatively, the COUNTA function can be used to count the non-blank cells.

Table of contents
  • 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 function 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.

How to Use COUNTIF Non-Blank Function?

#1–Numerical Values

The steps to count non-empty cells with the help of the COUNTIF function are listed as follows:

  1. In Excel, enter the following data containing both, the data cells and the empty cells.


  2. 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.

    Countif not Blank Example 1-1

  3. 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 not Blank Example 1-2

#2–Text Values

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

  • Step 1: In Excel, enter the data as shown in the following image.
Example 2
  • Step 2: Select the range within which data needs to be checked for non-blank values. Enter the formula shown in the succeeding image.
Example 2-1
  • 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.

Example 2-2
#3–Date 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,"<>"&"")”

Example 2-3
  • 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.

Example 3-1

COUNTIF Function in Excel Video

 

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

How is the COUNTIF formula used to count blanks?

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.

How does the COUNTIF function count the duplicate values?

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”).”