Sometimes, your Excel sheet could have empty cells by default or you format the data in a sense that some cells are left blank for clear visuals. Though this format is super effective to handle the data visually, however, you will face troubles while applying some functions on this sheet. It is because you will not be able to understand how many rows or columns you have in the sheet.
The COUNTIF or COUNTIFS are two basic functions that can help you count non-empty cells in Excel. Both functions can be used alternatively according to your data. You just have to specify the range in combination with the not equal operator and an empty value the find the count of cells that are non-blank.
How to Count Non-Empty Cells Excel with the COUNTA Function
Let’s understand first the Basic Situations in which the COUNTA function is helpful. In any case, when you have to count non-blank cells the COUNTA can help you. For instance, suppose you have a sheet containing a table with the names of some employees as well as comments of supervisors on all employees.
Basically, the main thing you need to find out here is the count of how many supervisors have written comments for employees. We will use the COUNTA feature to count the blank cells. You will see the comments are placed in cells B2 to B10. Here’s how you can do it:
Choose the cell that needed to add the formula output.
Open the formula bar and put the formula here:
=COUNTA(B2:B10)
Now, press ENTER key.
COUNTA will return a number as soon as you press ENTER from the keyboard. In this example, this number was of the employees who got a comment from the supervisor. And those who didn’t get a comment are left with an empty cell. Also, these employees were not added to the COUNTA function as they don’t have a comment in front of their names.
How to Count Non-Empty Cells Excel with Find & Replace Option
Other than the above method, we have another super easy trick, Find and Replace that can help in counting non-empty cells. You can apply this method when the Excel spreadsheet has a large table. You will find each value on one window together with cell addresses. Furthermore, when you click on an item’s name from the list you can easily move to another item.
Choose the range that you need to count the non-blank cells. Press CTRL + F hotkey.
Now, you will see the Find and Replace dialog box in which you have to put the asterisk symbol (*) in the Find what box.
Click on the Options button and choose the Values or Formulas item given in the Look in the drop-down list.
The tool will count all filled cells while ignoring the blank formulas when you choose Values.
On the other hand, when you select Formulas, you will see that the Find and Replace option will show up all the values.
Now, hit the Find All option and you will see the outcome. You will find each item and its quantity.
Now, you will be able to choose all items found on the panel of Find and Replace. Each non-blank cell will be highlighted.
How to Count Non-Empty Cells Excel with a Special Formula
Here comes another useful method that can help in finding the number of non-empty cells. In this method, you will see a slight difference and that is you will not be able to find the cells, however, you can choose the filled cells as per your needs. Below is the formula if you need to count all filled cells, formulas, constants, and cells with spaces:
=COUNTA()
Add the following formula when you need to find the number of cells containing constants:
=ROWS(L8:L11) * COLUMNS(L8:L11) –COUNTBLANK(L8:L11)
Here are the steps to follow:
Choose an empty cell from the sheet.
Add =COUNTA() or else you can add =ROWS() * COLUMNS() –COUNTBLANK()
Between the brackets, you can now easily add the range address manually. or else you can put the mouse cursor between the brackets and underline the cell range.
You have to add the range address 3 times with the following formula:
=ROWS() * COLUMNS() –COUNTBLANK()
Press ENTER from the keyboard. And you will see all the selected cells are filled with outcomes.
You can use the following formula when you need to count constants without cells but extra spaces:
=SUM(–(LEN(TRIM(range))>0))
Remember that the above formula is not a simple formula. It has to be added with CTRL + SHIFT + ENTER because it is an array formula.
Now, select an empty cell from the sheet.
Add =SUM(–(LEN(TRIM())>0))
Put the mouse cursor between the brackets and choose the cell range from the table. You will find the range address that appears in the formula.
Press CTRL + SHIFT + ENTER and you will find the number in the selected cells.
Below you can witness the effectiveness of these formulas while working with constants, extra spaces, and blank formulas.
That’s it!
You can count non-empty cells in Excel by using the above-mentioned methods easily. Just be careful while applying these steps, so that you can have a real outcome.
