How to Count Non-Blank Cells in Excel
Excel cells can house different data types, and sometimes you need to get the count of a specific data type in your Excel spreadsheet. Thanks to Excel’s arsenal of built-in functions, you may count any data type in your spreadsheets with Excel formulas.
Although it might seem a bit pointless to someone who isn’t in your shoes, there may be a scenario where you need to count the non-blank cells in your spreadsheet. So how do you count all the cells that contain any kind of value? Excel has just the right function for you.
Using the COUNTA Function in Excel
COUNTA is a built-in Excel function that looks through the values you feed to it and counts the number of values that aren’t blank. The syntax for COUNTA in Excel is as below:
You can insert the values directly or refer to a cell or a range that contains them. You can input multiple values for COUNTA to look through.
COUNTA is straightforward to use as it does only one thing and takes in a single argument. However, COUNTA isn’t very flexible about what it considers blank and what it considers data. As an example, a single space in a cell will qualify it as a cell containing data, and as a result, it will be counted by COUNTA.
Here are some things to consider when using COUNTA:
Basically, any sort of value or information in a cell makes it a non-blank cell in the eyes of COUNTA. If you’re looking for something more specific, you canuse COUNTIF and COUNTIFS to add your own conditions. This way, you can specifically tell your formula what to look for and count.
With all that said, if COUNTA is the function you’re looking for, then let’s move on to learning how you’re able to use COUNTA to get a count of non-blank cells in Excel.
How to Count Non-Blank Cells in Excel With the COUNTA Function
The different scenarios where COUNTA can be useful all boil down to a smaller scenario where you need to count non-blank cells. As an example, in this sample spreadsheet, we have a table containing the names of some employees and the supervisor’s comments on each employee.
The goal here is to count how many of the employees have been written a comment by the supervisor. Rather than counting the blank cells one by one, we’re going to kindly ask COUNTA to do it for us.
In this example, the supervisor comments that we want COUNTA to look through are placed in cells B2 to B10. Hence, we’re going to have COUNTA look through that range. Here’s how:
Once you pressEnteron your keyboard, COUNTA will instantly return a number. In our case, this number was the number of employees who had received a comment from the supervisor. This is because those who did not receive a comment had an empty cell in front of them and were not included in the COUNTA count.
Count Non-Blank Cells in Excel With Ease
Excel comes packed with an arsenal of count functions intended for different scenarios where you need to count specific cells. One such function is COUNTA which provides you with the number of cells that contain any type of value or data in a range.
COUNTA is truly selective in its judgment, as any cell that contains any sort of value (even a blank space) is qualified as a non-blank cell. You can combine COUNTA with other Excel functions to create formulas that seem like magic. Now that you know all about COUNTA, why not go ahead and give it a try?
Using a combination of the LEN function, we can count the number of words or characters in a cell. Here’s how to do it!
I gripped my chair the entire time—and then kept thinking about it when the screen turned off.
Freeing up vital memory on Windows only takes a moment, and your computer will feel much faster once you’re done.
Obsidian finally feels complete.
Lose your laptop without this feature, and you’ll wish you had turned it on.
Sometimes the smallest cleaning habit makes the biggest mess.
