How to count filtered cells

Answer

To count filtered cells in Excel, you must use functions that ignore hidden rows caused by filters. Standard counting functions like COUNT or COUNTA count all cells, including hidden ones.

Method 1: Using the SUBTOTAL Function

The SUBTOTAL function is designed to perform calculations on visible cells only.

1. Select the cell where you want the count to appear.

2. Enter the formula:

   =SUBTOTAL(103, range)

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

• range is the range of cells you want to count, e.g., A2:A100.

Example:

If column A contains 50 names and a filter hides 20 rows,

=SUBTOTAL(103, A2:A51)

will return 30, counting only the visible names.

Function Numbers for SUBTOTAL

• 102 → Counts numeric values in visible cells

• 103 → Counts non-empty cells in visible cells

Method 2: Counting Only Filtered Numbers

If the column contains numbers, use:

=SUBTOTAL(102, B2:B100)

This counts only the visible numeric values after a filter.

Method 3: Using AGGREGATE Function

The AGGREGATE function can count filtered cells with more flexibility than SUBTOTAL.

1. Select the target cell.

2. Enter the formula:

   =AGGREGATE(3, 5, range)

• 3 → COUNTA

• 5 → Ignore hidden rows and errors

• range → Range of cells to count

Example:

For column C, after filtering,

=AGGREGATE(3, 5, C2:C100)

returns the count of visible, non-empty cells.

Notes on Windows vs Mac

• Both SUBTOTAL and AGGREGATE work the same on Windows and Mac.

• Shortcut keys to open the function dialog may differ:

  • Windows: Shift + F3

  • Mac: Fn + Shift + F3

Tips

1. Use SUBTOTAL for simplicity and fast results.

2. Use AGGREGATE when you need to handle errors in filtered ranges.

3. Always check the filter to ensure only intended rows are counted.

This approach ensures accurate counts for filtered data without manual adjustments.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report