Excel Guides

Counting Filtered Rows in Excel

There are a few different ways to count filtered rows in Excel. One way is to use the COUNTIF function. This function takes two arguments: a range of cells, and a criteria. The criteria can be a number, text, or logical expression. For example, to count the number of cells in a range that contain the number 3, you would use the following formula:

=COUNTIF(A1:A10,3)

Another way to count filtered rows is to use the SUBTOTAL function. This function also takes two arguments: a range of cells, and a type. The type argument is a number that specifies which type of operation you want to perform. For example, type 1 will count all cells in the range, even if they're hidden by a filter. Type 11 will only count visible cells in the range.

=SUBTOTAL(1,A1:A10)  //counts all cells
=SUBTOTAL(11,A1:A10) //counts only visible cells

If you're using Excel 2007 or later, you can also use the COUNTA function to count filtered rows. This function counts all non-blank cells in a range. So if you have a column of data that has some blank cells, COUNTA will only count the non-blank cells.

=COUNTA(A1:A10)  //counts all non-blank cells

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.