How to Count Cells in Excel
(COUNT Function Guide)
Apart from storing and manipulating numbers, sometimes, you may want to use Excel to simply count numbers.
For example, the number of students in a class, the number of items on a list, etc.
To do so, Excel offers two functions, the COUNT and the COUNTA function. Both the said functions are super easy to operate and can prove very handy. 😀
Read through this tutorial till the end to master both these functions. Also, download our free sample workbook right here to practice alongside you read.
Table of Contents
How to use the COUNT function to count cells in Excel
The COUNT function of Excel is going to be one of the simplest functions you’d ever come across.
Take the data in the image below.
1. To count these cells, write the COUNT function as follows.
=COUNT (A1:A6)
2. Here is the count produced by Excel.
Must note how the COUNT function has ignored the blank cell in between.
Pro Tip!
Did you notice how the COUNT function gives results that are not obvious to the eyes? We can see there are five values in the above formula.
The COUNT formula, however, only returns 4 as the count.
This is because the COUNT function only counts certain values that are either:
- Numeric values
- Date and Time values
- Boolean values (TRUE/FALSE)
- Numeric values enclosed in quotation marks (like “10”)
Umbrella is a text value, hence ignored by the COUNT function.
3. If you do not have a cell range to specify, you may also specify values inside the formula separated by a comma.
=COUNT (1, 2, 12/12/22, hello)
Must know that Excel 2007 (and newer versions) can only accept up to 255 values (arguments).
Until Excel 2007, this number was only 30. 🤨
Kasper Langmann, Microsoft Office SpecialistCounting cells in non-adjacent ranges
Can the COUNT function tackle non-adjacent ranges like in the image below?
1. Write the following formula.
=COUNT (A1:A5, B1:B6, C1:C10)
We have specified each non-adjacent range as a separate argument.
2. The COUNT function counts all the non-blank cells.
COUNT vs COUNTA: Count numbers or non-empty cells
What differentiates the COUNT and COUNTA functions? A few things.
1. The COUNT function only counts cells containing specified values. These values include numbers, dates, time, and logical values (TRUE / FALSE). It also counts in numerical values enclosed in Quotation marks (“”).
Any value other than these would not be counted in by the COUNT function. It also ignores any text string.
2. The COUNT function ignores any blank cell in the specified range.
3. The COUNTA function only counts all non-blank cells.
A blank cell means a cell is all blank (no value). If a cell is only apparently blank, the COUNTA function would treat it as a non-blank cell and count it in.
An apparently blank cell may be the one with space even. Or some function the result whereof is a blank cell.
Kasper Langmann, Microsoft Office SpecialistHow to use the COUNTA function to count non-empty cells
It’s time we see how the COUNTA function works.
The COUNTA function counts all non-blank cells (containing any values).
Let’s apply the COUNTA function to the data in the image below.
1. Write the COUNTA function as below.
= COUNTA (A1:A10)
2. COUNTA gives the results as follows.
The results show 8 cells. However, we only see 7 cells that contain values.
That’s what you see. One cell in between doesn’t have a value but a space character.
Even if a cell has a space character, the COUNTA function doesn’t treat it as a blank cell. It is included in the count of cells. 😏
The COUNTA function counts all values including text values and ERROR values. Error values include errors posed by Excel like the #NAME! error etc.
Kasper Langmann, Microsoft Office SpecialistCount cells using the status bar
If you’re running out of time and want a quick count of cells containing data – this section is for you.
You can count non-blank cells in Excel without operating a function.
1. Select the cells containing data.
Navigate to the right bottom of your spreadsheet to see the count of cells.
2. Or you can select multiple column headers (or row headers). In this case, Excel will count the number of non-blank cells in each column (or row).
That’s it – Now what?
This guide teaches you the basics of the COUNT and COUNTA functions. It differentiates between them both and explains when should you use each of them.
Both the COUNT and COUNTA functions would help you in many situations. But these are only two very basic functions of Excel.
There’s much more to learn from this giant spreadsheet software. Some functions of Excel that you MUST learn are the VLOOKUP, SUMIF, and IF functions.
Register for my 30-minute free email course that is designed to offer you insights into these functions (and more).
Other resources
There are many advanced versions of the COUNT and COUNTA functions of Excel. For example the COUNTIF function and the COUNTIFS function.
The COUNTIF function counts cells that meet a single criterion. Whereas, the COUNTIFS function counts cells that meet multiple criteria. Check them out here!
Additionally, you may want to learn how to use operators in the COUNTIF Function.