by Alexander Frolov, updated on
This articles looks at the ways to count non-blank cells in Excel 365 - 2013. Below you'll find 3 methods for counting non-blanks: see the number on the Excel Status bar, employ the Find and Replace dialog or use a special formula.
Your table may have numerous blank cells left for better visualization. On the one hand, such layout is really convenient. On the other hand, it may prevent you from seeing the correct number of data rows. e.g. how many products are sold or how many people take part in a conference.
If you aim to count empty cells, you'll find a few quick ways in the above-linked article.
Below there are 3 options for counting non-blank cells in Excel.
Note. If a cell contains a formula that returns space between quotes (""), it's not seen as empty. I will refer to them as to blank formulas in this article.
Excel Status bar shows a number of tools you may find helpful. Here you can see page layouts, zoom slider and basic math functions displayed for numerical values.
To see how many selected cells contain data, just look at the COUNT option on the Status bar.
Note. This option will not work if you have only one filled cell in the selected range.
It's also possible to count non-empty cells with the help of the standard Excel Find and Replace dialog. This method is good if you have a large table. You'll get all values displayed on one window along with their cell addresses. In addition, you can easily navigate to any item by clicking on its name in the list.
Tip. You can now select all found items on the Find and Replace pane. You'll see all non-blank cells highlighted and it will stay after you close the window.
The third way to calculate the number of non-empty cells is to use an Excel formula. Though you will not see where the cells are, this option helps you choose what kinds of filled cells you want to count.
If you need to count all filled cells, constants, formulas, cells with spaces, you should use the formula =COUNTA()
.
To get the number of cells with constants and those including spaces, enter
=ROWS(L8:L11) * COLUMNS(L8:L11)-COUNTBLANK(L8:L11)
Follow these steps to apply the formulas:
=counta()
or =ROWS() * COLUMNS()-COUNTBLANK()
to the formula bar.With the formula =ROWS() * COLUMNS()-COUNTBLANK()
you need to enter the range address 3 times.
You will see the result in the selected cell.
If you want to count only constants without cells with extra spaces, use =SUM(--(LEN(TRIM(range))>0))
Please note, this is an array formula that needs to be entered with CTR + Shift + Enter.
=SUM(--(LEN(TRIM())>0))
in the formula bar.On the screenshot below, you can see a brief summary showing how these 3 formulas work with constants, blank formulas and extra spaces. In the test table I have a range with 4 cells selected. A2 contains a value, A3 has a formula that returns an empty string, A4 is empty and A5 has two spaces entered. Under the range, you can see the number of the found cells next to the formula I used to find them.
Another way to count non-blanks in Excel is using the COUNTIF formula =COUNTIF(range,"<>"&"")
. You will find the full details in this tutorial - COUNTIF for non-blanks.
Now three ways of counting non-blank cells in Excel are at your disposal. Just pick the one that suites you best. It can be the Status bar, Find and Replace or a formula. Be happy and excel in Excel!
Table of contents