# How to Count in Excel

There are lots of different ways to count things in Excel – maybe you need to count the numbers in a column, or all the data, or just the blank cells. Fortunately, there is a function for each of those:

• COUNT
• COUNTA
• COUNTBLANK

For example, to count the blank cells in the range A1:A5, use the following formula in cell A7:

=COUNTBLANK(A1:A5)

### More Complicated Counting

If you have more complicated things that you need to count, there are other functions to do the job:

• COUNTIF
• COUNTIFS
• SUBTOTAL
• AGGREGATE

For example, to count only the visible numbers, after filtering and/or manually hiding rows in a list, use a SUBTOTAL formula. This example uses 102 as the second argument, so it counts numbers only, in the visible rows (filtered or manually hidden).

=SUBTOTAL(102,B2:B10)

In the screen shot below, there are 5 visible numbers in cells B2:B10, and that is the result in cell B15, where the SUBTOTAL function is used.

The COUNT function, used in cell B12 in the screen shot below, returns 8 – it counts numbers in the hidden rows too.

### Watch the Slide Show

To see a quick overview of 7 ways to count in Excel, you can watch this short slide show. It also contains a video on using the COUNTIFS function. You can see more examples on my Excel Count Functions page, and download the sample file.

_____________________________

___________________

### 14 Responses

1. James says:

Hello Debra,

Many thanks for your extremely comprehensive (and very handy) Recap …!!!

A great Fan of your talent for clarification …which makes everything “digestible” …

2. Paul says:

Thank you very much for clear guides. You make my learning of Excel easier and faster!

3. Kevin says:

Omitted are the DCOUNT and DCOUNTA.

4. mahmoud says:

Thanks for the added value

5. Mike Rudzinski says:

I don’t think I’ve ever used AGGREGATE. Interesting that it can ignore error values…

• @Mike, yes, AGGREGATE is a great function, as long as everyone has Excel 2010 or higher.
You can see the list of its functions and options in this blog post

6. Larry Anderson says:

Dear Debra,

I am doing Genealogy work on an Excel file in which I have over 16,500 names, with birth and death information. The names are in Column A, they are all listed alphabetically starting with the last name first, separated by a comma. I would like to extract the last name only. However, there is one slight problem. I may have 200 last names of Anderson, Davis, Foster, etc…. I would like for there to be only one last name showing in Column D of Anderson, Davis, Foster, etc…. This would allow me to print a complete listing of last names for each unique last name. Can Excel perform this type of function?

Thanks

Larry Anderson

• Randy says:

When you have all of the last names in column D, go to the “Data” tab and select “Remove Duplicates”. You may want to copy the Column D data to a separate worksheet to avoid loosing the data in Columns A, B, and C.

7. Virendra says:

If you select only column D and do not expand the selection to include columns A to C then they will not be affected while removing duplicates.

8. Bhawesh says:

Dear Sir,
My question is that lets suppose I have 12 combo boxes & 2 dropdown box. When I select dropdown box No.1 which is link with dropdown box 2, values are populated in the first four combo boxes, but the same value populate in another combo boxes too. I would like to know that is there any trick to populate different values in every paired combo boxes. Kindly suggest. REgards.. Bhawesh

9. Ashutosh jaiswal says:

your all farmulla’s are very useful thanks once again…..

1. March 30, 2015

[…] How to Count in Excel […]