Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to Count Non Blank or Non Empty Cells in Excel

When you enter data in large data sets or tables in Excel, some cells are left blank or empty due to unavailability of related data or better visualization of data cells. Later when you need count of cells where data is entered, then you need to use quick and efficient methods to do so in Excel. So you need to ignore blank or empty cells and count Non Blank or Non Empty cells in your targeted data range. Before doing so you need to have clear understanding about Non Blank and Non Empty cells.  

Non Blank or Non Empty cells are those which contain values (Number or text), logical value(s), space(s), formula(s) that return empty text (“”), or formula errors. If a cell contains any of these mentioned values or argument, it will be considered as Non Blank or Non Empty cell.

In Excel a user can count Non Blank or Non Empty cells in a number of ways, but each method has its own interpretation to count such cells. Some methods do not count a cell as Non Blank that has a formula returning empty text (“”). So there might be difference between results of each method based on this interpretation.

Suppose you have data set of students with their grades. Some cells have grades values in it, some cells are empty or have empty text (“”), as shown below

In Excel you can count all those students who have been allotted with grades by using these three methods.

Using Excel Status Bar Count

Excel Status Bar shows the count of values when you select a range of cells. It gives you total count of cells that have values, logical values, space(s), empty text (“”) or formula errors. In this example, you can see that apparently there are 4 cells that have grades’ values in it but status bar count shows 5 values. It’s because 1 cell has a formula returning empty text (“”). So you need to be careful with such values as status bar count also includes these empty text values in total count figure.

Using Excel Find and Replace functionality

Find and Replace is very handy tool to count Non Blank cells. It is very helpful in large data sets. It’s not only shows count of Non Empty cells but also gives you cells’ references. It’s also have option to count only values or values and formulas both.  

After selecting cells’ range you need to press Ctrl+F short keys to display Find and Replace dialog. You need to enter * in Find what: field.

Next, you need to click on Options >> button and then select one of options from Look in: drop down list and select Find All button.

  • If you select Values from drop down list, then it will give you count of cells that have values only and ignore all those cells that have formulas returning empty text (“”). As you can see this option only counts grades’ values and ignores blank formula cell.

  • If you select Formulas from drop down list, it will give you count of cells that have both values and formulas. As you can see this option gives you count of cells having grades’ values and blank formula cell, along with details of cell addresses having values and formula too.

Using Excel Formulas to count Non Blank cells

There are multiple formulas to count Non Blank or Non Empty cells. Each formula is designed to count such cells based on what kind of filled cells you need to count. Here we will discuss these formulas in details.

 

  • COUNTA function

 

Excel COUNTA function is designed to count all those cells that are filled with values (both text and number), formulas (returning empty text (“”) or values), spaces, or formula errors. Following formula gives you count of students who have grades’ values.

=COUNTA(B2:B7)

 

  • COUNTIF function

 

Excel COUNTIF function counts cells that meet the condition Not Equal to empty (“<>”&””). This formula also counts cells that are filled with values, formulas, spaces, or formula errors. In this example, It will give you total count of cells where student have grades and also cells that have even blank formula, such as;

=COUNTIF(B2:B7,"<>"&"")  

 

  • SUMPRODUCT, LEN and TRIM functions

 

If you want to ignore cells that are filled with only spaces and formulas returning empty text (“”), and only want to count those cells that are filled with values then you need to combine these three functions in single formula in following way;

=SUMPRODUCT(--(LEN(TRIM(B2:B7))>0))

Here, TRIM function eliminates spaces from cells in range, LEN function return counts of character length in each cell of selected range. Double Hyphen symbol (–) converts cells’ values into 1s that have character length greater than zero. And cells which character length is zero, they are converted into 0s. Finally SUMPRODUCT function sums these 1s and 0s to return total count.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
i have a table with Dates, First names , Last names and i want to count how many times a name occurs, but if a name occurs more than once on a particular date i need to only count it once. this is on excel and not google sheets
Solved by F. H. in 31 mins
I need a formula to count the number of rows that contain at least one of two possible phrases, but not give me the total number of instances that both phrases occur overall.
Solved by E. B. in 33 mins
hi, i have this formula =SUMPRODUCT((B4:B464<>"")/COUNTIF(B4:B464,B4:B464&"")), i want to add a subtotal to it. so when I filter the column it also sums up the unique number if enteries i have filtered on.
Solved by F. L. in 60 mins
I need cell B2 Green if cells G2:Q2 are empty. If cells G2:Q2 have any text in any cell then i need B2 to be red.
Solved by C. W. in 20 mins
Need a formula that counts (1,2,3,4 ect..in cell M21) a range of cells, N4:N20, which are annual premium values. But I only want to add a sale count if the average monthly premium is above $30 per sale. So if someone has 4 sales but they don't equal a total of $120/mth ($30 x 4 = $120)... they would only get a count of 3 sales.
Solved by S. E. in 60 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc