# 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.

_____________________________

___________________

Hello Debra,

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

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

Thanks James! I’m glad you found it helpful.

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

Omitted are the DCOUNT and DCOUNTA.

Thanks Kevin!

Thanks for the added value

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

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

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.

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.

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

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