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.
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.
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
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.
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
=COUNTA(D2:D11)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
- 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.
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:
=ROWS(A2:A7) * COLUMNS(A2:A7)-COUNTBLANK(A2:A7)
To only count cells with constants as non-blank in A2:A7, use the following formula:
=SUM(--(LEN(TRIM(A2:A7))>0))
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
=COUNTIFS(D2:D11,"<>") 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.
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
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
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.