Which of the following functions counts the number of cells that are not blank within a specified range of cells count Countblank Counta sum?

Home ➜ Excel Formulas ➜ Count Total Number of Cells from a Range

In Excel, you can use two different formulas if you want to count the number of cells from a range (blank and non-blank cells). In this tutorial, we are going to look at both formulas.

Count Number of Cells (Method-1)

In the following, example we have a few values in column A, and you need to count the cells from the range A1:B10. And for this, we will use the ROWS and COLUMNS function.

Steps to write the formula:

1. First of all, in cell D1, enter “=ROWS (A1:B10)”.
2. Next, type the asterisk sign (*) to multiply the values.
3. After that, you need to enter the COLUMN function by referring to the same range (COLUMNS(A1:B10).
4. In the end, press enter button to the result in the cell.

Now as you can see, when you hit enter, it returns 20 in the result as you have a total of 20 cells in the range you have specified.

Now let’s try to understand the formula that we have used here. In the first part, we have used the ROWS function which returns the number of rows that you have in a range. Here it returns 10 as you have ten rows in the range A1 to B10.

Now when next you have the COLUMNS function that returns the number of columns you have in a range. Here it returns 2 as you have two columns in the range A1 to B10.

In the end, you have an asterisk sign between both the values to multiply rows and columns to get the total number of cells in the range.

Combine COUNTA – COUNTBLANK to Count Cells from a Range

The second method wants you to combine COUNTA and COUNTBLANK functions. By combining both functions you can count all the non-blank cells and all the blank cells from a range. Below we have the same example that we have used in the first example.

In the cell D1, you can enter the following formula:

=COUNTA(A1:B10)+COUNTBLANK(A1:B10)

As I said, this formula works in two parts:

1. COUNTA function counts the cells where you have values.
2. COUNTBLANK function counts the cells where you don’t have values.

In the end, you have a “+ sign” which sum both number and returns you the total count of cells that you have in the range that you have specified.

🔙 Excel Formulas

More Formulas

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

   

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.

   

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.

• 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

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

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

Recommended Articles

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 VBA
• COUNTIF with Multiple Criteria
• VLOOKUP Errors
• Use Not Equal to in Excel
• XML in Excel