The COUNTA function counts the number of cells that are not empty in a range
COUNTA is a very simple formula that can be used to create much more powerful formulas. If a cell in your selected range is not empty, Excel will class that as ‘1’ and then total up all the 1’s to produce the answer.
You can add up to 255 ranges into your statement by separating each one with a comma e.g
=COUNTA(range1, range2, range3) etc…
Although if you were ever in a situation where 255 ranges needed to be counted it might be an idea to investigate other options to help you
COUNTA in Action
COUNTA is different from COUNT as it will count non-numerical values. If you have a cell in the range that has a formula which is returning a blank value e.g “ “, this will also be counted in the range.
In the below image the formula is counting the number of non-empty cells in the range A1:A8
The result of which is 8:
Why is this formula useful?
Imagine that you have a spreadsheet with two tabs; a data tab and your main tab. The data tab has been extracted from your system but your main tab has formulas and formatting to manipulate the data into the correct format.
How are you going to know how far down to drag your formulas to pick up the new data extracted from the system? Simple, use a COUNTA formula to count the number of non-blank cells in a selected column of the main tab and deduct the answer from the number on non-blank cells in the corresponding column in the data tab. For example
The result is the number of rows your formulas need to be dragged down by.
Can you think of any examples where COUNTA can be useful?