How to Count Non Blank Cells in Excel – 3 Quick Ways
Count Non Blank Cells in Excel helps you to ignore blank ones and focus only on cells with values that matter to you.
Some cells in an Excel worksheet may look blank but aren’t actually empty, which can be confusing. Sometimes, formulas return a blank value, text matches the cell’s fill color, or invisible values are added by VBA macros. These cells seem empty but contain hidden content that can cause issues in your work.
In this article learn How to Count Non Blank Cells in Excel.
Method 1:- Count Non Blank Cells in Excel Using COUNTA Function
To count cells with data, remember that “data” includes visible text and numbers, but also spaces, which might be hard to see. Manually counting blank cells in small ranges is possible, but with large data tables like the ones shown in Projects 1 and 2, it becomes challenging.
Here’s how to use the COUNTA function:
Determine the Range: Select the range of cells you want to count. For example, in Project 1, you might use the range
B3:G12.Choose a Result Cell: Decide where you want to display the count result.
Enter the Formula: In the result cell or formula bar, type
=COUNTA(B3:G12)and press Enter. This will count cells with any data, including spaces.
To count cells across multiple ranges, you can specify multiple areas. For instance, to count data in both Project 1 and Project 2 tables, you could use:
=COUNTA(B3:G12, B16:G25)
This formula will return the total count of non blank cells in excel data or containing data in the specified ranges.
Method 2:- Count Non Blank Cells in Excel Using COUNTIF Function
Let’s look at how to use COUNTIF to count non-empty cells effectively.
Select the Cell where you want the result to appear, such as CJ13.
Enter the COUNTIF Formula by typing:
Here,
B3:G12is the range you’re counting in, and"<>"&""tells Excel to count only cells that are not empty.- Press Enter. Excel will display the count of non-empty cells in the selected range.
This formula is helpful for large data sets, where manually counting non-blank cells would be too time-consuming.
Method 3 :- Count Non Blank Cells in Excel Using SUMPRODUCT Function
In this example the data set is showing sales of two projects of different stores. Lets Count Non Blank Cells Using SUMPRODUCT Function.
Here’s how to do it:-
- Enter the formula where you want to get result :- =SUMPRODUCT(–(B3:G12<>””))
- Here, B3:G12 is the range to count non blank cells in Project 1.
- For Project 2 to count non blank cells the Formula is =SUMPRODUCT(–(B16:G25<>””))
- Press Enter to get the result.
This method quickly shows you how many non blank cells are in both projects.
Download Practice File
Hope you like this Article, please leave a comment below.
