Future-Proof Your Career, Master Data Skills + AI

Blog

Future-Proof Your Career, Master Data Skills + AI

Blog

5 Ways How to Count Non-Blank Cells in Excel: Step-by-Step

by | 3:58 pm EDT | April 25, 2023 | Excel

There are times when you want to count the number of non-blank cells in a list in Excel and by excluding empty cells, you can get a more accurate picture of your data.

The most common method to count non-blank cells in Excel is to use the COUNTA function with the range of cells to be evaluated. Other methods include the COUNTIF function and the Find-And-Replace tool.

This article shows you how to count non-blank cells in Excel with five methods:

  1. the COUNTA function

  2. combining SUM and COUNTA

  3. COUNTBLANK

  4. COUNTIF

  5. Find-And-Replace

You may not be familiar with some of these Excel formulas but our examples show exactly how to use them.

1. Using The COUNTA Function

The COUNTA function is a built-in Excel function that counts cells that are not empty. It provides the opposite results to the COUNTBLANK function.

It works on ranges that contain any type of data, including text, numbers, and formulas. The function accepts a list of up to 255 values. The list can be a range of cells.

This is the syntax: COUNTA(value1, [value2], …)

Let’s say you have a list of salespeople and their sales figures for the month:

[Create Table: Salesperson Sales John 10000 Jane Bob 5000 Sue 7500 Mike]

To count the number of salespeople who made a sale, type this formula into an empty cell and press enter:

=COUNTA(B2:B6)

This formula counts the number of cells in the range B2:B6 that are not empty. In this case, the result would be 3, since only three salespeople made a sale.

The picture below shows an Excel spreadsheet with the function in the formula bar.

how to count non blank cells in Excel

Why Not Use The COUNT Function to Count Non-Empty Cells?

If you’re familiar with the more commonly used COUNT function, you’ll probably know that we could use COUNT in the above example and get the same result (i.e. 3 filled cells).

The difference is that it will count cells that only include numerical values. It ignores text or logical values (TRUE or FALSE).

Suppose we replaced the sales numbers with text values like 10K, 5K, and 7.5K.

The COUNT function will return a zero, while the COUNTA function shows 3 non-blank cells.

How to Count Multiple Lists

You can also use the COUNTA function to count each non blank cell in more than one range.

For example, if you had another list of salespeople and their sales figures for a different month, you could use the following formula:

=COUNTA(B2:B6, D2:D6)

This formula counts the number of selected cells in the range B2:B6 and D2:D6 that are not empty.

In the case of the illustration, the result would be 5. That’s because five salespeople made a sale in either of the two months.

Using COUNTA on two lists of figures with some empty cells

How to Count Distinct Values

After you count non empty cells in a large table, you may want to count the distinct values. This video will show you how:

2. How to Use SUM And COUNTA Together

You can also achieve the same results by combing the SUM function with the COUNTA function to count the number of non-blank cells in several ranges.

Using the data in the previous example, the formula looks like this:

=SUM(COUNTA(B2:B6), COUNTA(D2:D6))

The COUNTA function is used to count the number of cells that are not blank in each of the two ranges, B2:B6 and D2:D6.

Then, the SUM function is used to add up the two counts to get the total count of non-blank cells in both ranges.

Combing SUM and two COUNTA functions

3. How to Use The COUNTIF Function

The COUNTIF function in Excel counts the number of data values in a range that meets a specific condition or criteria. It takes two arguments: the range of all the cells you want to count, and the criteria you want to apply.

This is the syntax: =COUNTIF(range, criteria)

The criteria can include logical operators. Using our sample data, we can count the number of non-blank cells with this COUNTIF formula:

=COUNTIF(B2:B6,”<>”)

The first argument is the range. The second argument uses the <> operator to specify the “not equal to” condition. The two quotation marks with nothing in between specify an empty string.

Using the COUNTIF function on a list of numbers with some empty cells

4. How to Use The SUMPRODUCT Function

The SUMPRODUCT function is an array formula used to multiply two or more arrays of values and return the sum of their products.

An array refers to a list of values. The cell range that contains the first set of sales figures is one array. The array that contains the other set is a second array.

This is the syntax: =SUMPRODUCT(array1, [array2], [array3], …)

The SUMPRODUCT function can also be used to count non empty cells and blank formulas.

Example

Taking our list of sales figures, we can count the non empty cells using this formula output:

=SUMPRODUCT((B2:B6<>””)*1)

The formula works by using the not equal operator to skip an empty cell in the range address.

Using the SUMPRODUCT function on a list of numbers with some empty cells

5. How to Use Find And Replace

This method doesn’t use Excel formulas. Instead, you use the Find And Replace dialog box with these steps:

  1. Select the column or range you want to check.

  2. Open the Find And Replace dialog box (press ctrl + f ).

  3. Enter the asterisk symbol in the “Find what” input box.

  4. Click on the “Options” button to expand the dialog box and show more options.

  5. Make sure the “Look in” field is set to “Values”

  6. Check the “Match entire cell contents” option.

  7. Click on the “Find All” button.

Find And Replace dialog box with asterisk symbol

Excel will then display a list of all the non-blank cells in the selected range with their cell addresses. You can use this list to navigate to the non-blank cells or to select them all at once.

A Little Recap

In this article, we’ve given you 5 actionable ways how to count non-blank cells in Excel. Sure, there could be more, but we’ve found these methods to be the most useful an effective, and hope you do too!

Related Posts