Skip to main content

Count Non-blank Cells in Excel - Step by Step Tutorial

This tutorial shows five methods of counting non-blank cells in excel. All the methods are super easy to follow and take less than 10 seconds to get the result.

Count non-blank cells with Excel Status bar

Excel Status bar is a thin bar at the bottom of the Excel window. It is useful when counting the non-empty cells in Excel.

Just select the range you need to calculate the non-blank cells, then check the Count option on the Status bar. Then you can see the number of non-empty cells.

 Note: If there is only one filled cell in the selected range, the Count option will not work.

One click to count and select non-blank cells with Kutools

The Select Nonblank Cells feature of Kutools for Excel can calculate and select the non-empty cells with only one click. It's super easy and quick!

Go to the Kutools tab, and click Select > Select Nonblank Cells in the Editing group.

The Kutools for Excel dialog pops up, showing the message “6 nonblank cell(s).” At the meantime, the non-empty cells are selected.

Note: Before applying Kutools for Excel, please download and install it firstly.

Count with Excel formulas

In this section, we introduce the COUNTA function and COUNTIFS function to count cells that are not empty.

Use COUNTA function

Step 1: Input COUNTA formula

Note: D2:D11 stands for the data range in which you want to count the non-blank cells.

Step 2: Press Enter key to return the result

  1. The COUNTA function is simple to use with just one argument, but it has its limitations. It treats any value or information in a cell as non-blank, including empty strings ("") and blank formulas.
  2. Advanced formula examples

    Suppose you have a range of data in A2:A7 containing different types of values.

    To only count cells with constants and spaces as non-blank in A2:A7, use the following formula:


    To only count cells with constants as non-blank in A2:A7, use the following formula:


Use COUNTIFS function

Another way of counting non-blank cells is using the COUNTIFS function. The COUNTIFS function counts the number of cells in a range that meet one or more criteria.

Step 1: Input COUNTIFS formula

Note: D2:D11 stands for the data range in which you want to count the non-blank cells.

Step 2: Press Enter key to return the result

Note: Similar to the COUNTA formula, the COUNTIFS formula also considers any value or information in a cell as non-blank. If your data contains cells with different value types, you can use advanced formulas to specify the types of filled cells that you want to count.

Count non-blank cells with Find and Replace tool

Using Find and Replace tool is also a good way to calculate the number of cells that are not empty. In addition, you can get all values and their cell addresses displayed in one window, and easily navigate to any item by clicking it in the list.

Step 1: Choose the data range D2:D11

Step 2: Use the Find and Replace tool to return the result
  • Press the Ctrl + F short key (or click Find & Select > Find in the Editing group under the Home tab), then the Find and Replace dialog box pops up.
  • Enter the asterisk symbol ( * ) in the Find what field.
  • Click the Find All button.

After that, you will see the number of non-blank cells on the pane, which shows “6 cell(s) found”.

The Best Office Productivity Tools

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations