Usually we think of adding numbers on a spreadsheet, but sometimes you just need to count the items in a list. If you have tried to use Excel’s COUNT() function you may or may not have gotten the results you wanted.
There are two very similar functions in Excel: COUNT() and COUNTA(). The difference between them is that COUNT only counts cells containing numbers but COUNTA counts all cells that aren’t empty. Think of it as “Count Anything”. Speaking of empty cells, there is also a COUNTBLANK() function that will count only empty cells.I know I said “two ways to count”, so think if this as a bonus.
The syntax is the same for all three and is very simple. The only thing you need in the parentheses is the range of cells you want to count. You can also include multiple ranges by separating them with commas. For example: =COUNT(A1:A5,G10:G13) would look at cells A1 through A5 as well as cells G10 through G13.
So to show how each function works, here you can see formulas for COUNT, COUNTA, and COUNTBLANK.
| COLUMN A | COLUMN B | COLUMN C | |
| ROW 1 | 1 | a | 1 |
| ROW 2 | 2 | b | 2 |
| ROW 3 | 3 | c | 3 |
| ROW 4 | a | ||
| ROW 5 | b | ||
| ROW 6 | c | ||
| ROW 7 | =COUNT(A1:A6) | =COUNT(B1:B6) | =COUNT(C1:C6) |
| ROW 8 | =COUNTA(A1:A6) | =COUNTA(B1:B6) | =COUNTA(C1:C6) |
| ROW 9 | =COUNTBLANK(A2:A6) | =COUNTBLANK(B2:B6) | =COUNTBLANK(C2:C6) |
And here you can see the result of each formula:
| COLUMN A | COLUMN B | COLUMN C | ||
| ROW 1 | 1 | a | 1 | |
| ROW 2 | 2 | b | 2 | |
| ROW 3 | 3 | c | 3 | |
| ROW 4 | a | |||
| ROW 5 | b | |||
| ROW 6 | c | |||
| ROW 7 | 3 | 0 | 3 | <–COUNT |
| ROW 8 | 3 | 3 | 6 | <– COUNTA |
| ROW 9 | 3 | 3 | 0 | <– COUNTBLANK |
Now you know how to count how many numbers are in a list, how many of anything are in a list, or even how many of anything aren’t in a list. If you still can’t get the count that you need, try reading my previous tip about using COUNTIF to count items that meet specific conditions.
