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)

count blank cells www.contextures.com

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.

count visible numbers www.contextures.com

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.

_____________________________

Learn 7 ways to count in Excel, with function tips and tricks http://blog.contextures.com/archives/2015/03/26/how-to-count-in-excel/

___________________

You may also like...

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…

  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 […]

Leave a Reply

Your email address will not be published. Required fields are marked *