Basic Microsoft Excel Functions: COUNTA

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.

COUNTA Syntax

=COUNTA(range)

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

COUNTA
Click on the image to expand it

The result of which is 8:

COUNTA
Click on the image to expand it

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

=COUNTA(Data!A:A)-COUNTA(Main!A:A)

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?

If you’ve found this article interesting and or useful please share it on social media using the handy share buttons

Subscribe to our mailing list to be the first to know when new articles are published

At Happy Duck we build bespoke business and financial management tools for small, medium and large companies that help them achieve their goals. This makes us happy. Find out more about us and about our services.

More in Excel Basics

0 Comments

Leave a comment

Your e-mail address will not be published. Required fields are marked *