Future-Proof Your Career, Master Data Skills + AI

Blog

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.

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

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

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

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

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

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!

## How to Calculate Percentile in Excel: 4 Great Methods

Are you looking to calculate a percentile in Excel? Well, you're in the right place. To calculate a...

## How to Find Relative Frequency in Excel: 4 Quick Methods

Calculating relative frequency is a fundamental concept in statistics, providing valuable insights into...

## How to Interpolate in Excel: User Guide With Examples

In data analysis, interpolation plays a crucial role in estimating values that fall between known data...

## How to a Take Screenshot in Excel: A Guide for Windows & Mac

As a data analyst or a spreadsheet enthusiast, you’re probably familiar with the many ways Excel can...

## How to Add Numbers in Excel: 3 Top Methods Explained

One of the fundamental operations you perform in Excel is adding numbers. Whether you’re managing...

## Excel Showing Formula Instead of Result? 5 Quick Fixes

Have you ever found yourself in a situation where instead of showing the results of your calculations,...

## How to Hard Code in Excel: 5 Easy Methods Explained

Have you ever wanted to input a value into a cell in Excel and make sure that it remains unchanged, no...

## How to Copy a Formula Down in Excel: A Quick Guide

Copying formulas down in Excel is a handy skill that can save you a lot of time and effort! Whether...

## How to Remove Time from Date in Excel: 4 Quick Ways

When you’re dealing with dates and times in Excel, you might find it necessary to separate the time...

## How to Sum a Column in Excel: 5 Ways (Shortcut Included)

One of the best things about Excel is how quickly and easily it can add up all the numbers in a column....

## How to Calculate Skewness in Excel: Formulas Explained

Do you want to calculate the skewness of your data to understand its distribution? Well, you're in the...

## How to Remove Apostrophe in Excel: 4 Quick Ways

Apostrophes have their place in Excel; however, when not needed, they can clutter your data and make it...