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.

Count Specific Items With COUNTIF Function

This video shows how to use the COUNTIF function to count cells that
contain a specific string of text, such as “Pen”.

_____________________________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.