How to count filtered rows in excel

Answer

To count filtered rows in Excel, use a function designed to consider only visible cells. Regular COUNT or COUNTA functions include hidden rows, so a different approach is required.

Using the SUBTOTAL Function

SUBTOTAL counts only visible rows after applying a filter.

1. Click on the cell where you want the count.

2. Enter the formula:

   \(=SUBTOTAL(103, A2:A100)\)
   

   • 103 is the function number for counting visible, non-empty cells.

   • A2:A100 is the range of cells you want to count. Adjust this range as needed.

3. Press Enter. The result displays the number of filtered rows that are visible.

Notes:

• Use 102 instead of 103 to count numeric values only.

• The function automatically updates when you change the filter.

Example

If you have a table of 50 employees and filter by "Department = Sales," =SUBTOTAL(103, B2:B51) counts only employees in the Sales department that are currently visible.

Using the AGGREGATE Function (Alternative)

AGGREGATE is more flexible and works similarly to SUBTOTAL.

1. Select the cell for the count.

2. Enter the formula:

   $=AGGREGATE(3,5,A2:A100)$
   

   • 3 is the function number for COUNTA.

   • 5 ignores hidden rows.

   • A2:A100 is the range of interest.

3. Press Enter. The formula counts only filtered, visible rows.

Difference Between Windows and Mac:

• SUBTOTAL works identically on both platforms.

• On Mac, ensure that the range does not include entire column references like A:A for filtered data. Use a specific range like A2:A100 for accurate results.

Tips

• Place the count cell outside the filtered range to avoid accidental inclusion in the filter.

• Combine SUBTOTAL with other Excel functions to count specific criteria within visible rows.

This method ensures accurate counting of visible, filtered rows while ignoring hidden or collapsed data.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report