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.

_____________________________