How to sum visible cells only

Answer

To sum visible cells only in Excel, use the SUBTOTAL or AGGREGATE functions. These functions ignore hidden rows caused by filters or manual hiding, unlike the standard SUM function.

Using SUBTOTAL

1. Select the cell where you want the result.

2. Enter the formula:

   =SUBTOTAL(109, A1:A20)

   Explanation:

   • 109 is the function number for SUM that ignores hidden rows.

   • A1:A20 is the range you want to sum.

3. Press Enter. Excel will sum only the cells that are visible.

Notes:

• If you manually hide rows, SUBTOTAL will skip them when using 109.

• Using 9 instead of 109 will sum all cells, including hidden ones.

Using AGGREGATE

1. Select the cell for the result.

2. Enter the formula:

   =AGGREGATE(9, 5, A1:A20)

   Explanation:

   • 9 is the function number for SUM.

   • 5 is the option to ignore hidden rows and error values.

   • A1:A20 is the range to sum.

3. Press Enter. This method works for both filtered data and hidden rows.

Key Differences Between SUBTOTAL and AGGREGATE

• SUBTOTAL ignores hidden rows with specific function numbers (100+), but it cannot ignore errors unless combined with additional formulas.

• AGGREGATE is more flexible: it can ignore hidden rows, errors, and nested SUBTOTAL/AGGREGATE functions.

Example

Suppose column B contains sales amounts from B2:B10. Some rows are filtered out.

• Using =SUBTOTAL(109, B2:B10) will sum only visible sales.

• Using =AGGREGATE(9, 5, B2:B10) will give the same result, skipping hidden rows.

This ensures your totals reflect only the data currently visible, maintaining accuracy when filtering or hiding rows.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report