If you just want a quick count of the number of items in a list or a range of cells you can simply select the range (with your mouse), and look at the Status Bar at the bottom right of your Excel window.
This will count all cells that are NOT blank in your selected range.
Or for something more permanent you could use the COUNT, COUNTA or COUNTBLANK function depending on your needs.
They’re all very straight forward so let’s take a quick look at each one and I’ll show you some examples at the end.
Excel COUNT Function
COUNT Function Syntax
=COUNT(value1, [value2], …)
Where ‘value’ can be a single cell (you wouldn’t do this though as I hope you can already count to 1, if not my 2 year old can teach you, or for advanced counting my 5 year old says he can count to infinity!) or, more likely you will enter a range of cells in place of each ‘value’.
For example; you can count one range of cells:
=COUNT(A1:A500)
Or multiple ranges of non contiguous cells:
=COUNT(A1:A500,C1:C500,E1:G500)
There can be up to 30 ‘values’.
COUNT Function Rules
- It only counts cells containing numbers
- It ignores blank cells
- It ignores cells containing anything but a number
Ok, that’s 3 ways to say the same thing but it leads me nicely onto the COUNTA function.
Excel COUNTA Function
Excel’s COUNTA function counts cells that are not empty.
That means it includes error values, like #VALUE!, numbers and blank spaces. I don’t mean blank cells, I mean cells with empty text like for example if you entered a space in a cell then COUNTA would count that cell.
COUNTA doesn’t count empty or blank cells. You need the COUNTBLANK function for that. More on COUNTBLANK below.
COUNTA Function Syntax
=COUNTA(value1, [value2], …)
Ditto COUNT function formula examples. That is; the ‘value’ in the COUNTA function syntax works the same as they do for the COUNT function.
Excel’s COUNTBLANK Function
COUNTBLANK Function Syntax
=COUNTBLANK(range)
You’ll notice that the syntax is ‘range’ and there’s only one of them. This is because unlike COUNT and COUNTA, the COUNTBLANK function cannot handle non-contiguous ranges.
The solution to this is to add COUNTBLANK functions together like this:
=COUNTBLANK(A4:B10)+COUNTBLANK(D4:D10)
COUNT, COUNTA and COUNTBLANK Examples
In rows 11-13 you can see the different results each formula returns depending on the Function used.
Download the workbook and reverse engineer the formulas.
Other ways to COUNT in Excel
- Use a Pivot Table
- Use an Excel Table and insert a COUNT total
- Use the SUBTOTAL Function
Or, if you want to count cells that match specific criteria then take a look at the COUNTIF and COUNTIFS functions.
