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.
Leave a Comment