Microsoft Excel Functions & Formulas

Use the SUBTOTAL function to count all visible

rows in a filtered list

This example shows a filtered list. The task is to count all visible

and used rows. Note that the COUNT and COUNTA functions can

also be used in a non-filtered list. However, they also count hidden

rows. To get the right result, use the SUBTOTAL function and use

“3” as the function_num value (see this function’s syntax in the

previous tip).

4

To count all visible rows in a filtered list:

1. Generate a filtered list like the one shown in Figure 6-29.

2. Select cell C13 and type the following formula:

=SUBTOTAL(3,B2:B10).

3. Press <Enter>.

174

Chapter 6

Figure 6-29