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

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!

## Mastering Excel Shortcuts: A Complete Guide

Unlock the full potential of Excel with this comprehensive guide to mastering shortcuts. Improve your productivity and streamline your workflow with key combinations.

## Comprehensive VBA Inventory Management System

A project focused on implementing an extensive VBA-based inventory management system within an Excel spreadsheet to streamline data handling.

## Mastering VBA Arrays for Spreadsheet Automation

This project aims to streamline and boost efficiency in Excel spreadsheet tasks using VBA arrays.

## Mastering Data Analysis with Pivot Tables in Excel

Learn to analyze and summarize complex data using Excel’s powerful Pivot Table feature.

## Financial Data Management with VBA

This project teaches how to manage and automate financial data in Excel using VBA programming.

## Does Excel Work On Mac? How is it Different to Windows?

Hey there, fellow Mac users! Have you ever wondered how you can level up your data management game with...

## 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 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 Interpolation in Excel

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

## How Long Does it Take to Learn Excel?

So you're looking to learn Excel, one of the most widely used software applications in the...

## How Can I Learn Excel Quickly: A How-To Visual Guide

Need to learn Excel quickly? You've come to the right place! In this quick guide, we will get you up to...

## How to Add Power Query to Excel: A Step-by-Step Guide

Power Query is an incredibly useful tool for Excel users looking to import, connect to, and shape...