Answer

Summing only visible cells means Excel returns a total that ignores values in hidden rows or filtered rows. Excel performs this operation through functions that recognize visible cells only.

How to sum only visible cells in Excel

To sum only visible cells in Excel:

1. Use the SUBTOTAL function

The SUBTOTAL function processes filtered or manually hidden rows depending on the function number.

Function structure\text{SUBTOTAL}

(\text{function\_num}, \text{range})

Most used function numbers

1. 109 returns a sum and ignores hidden rows and filtered rows.

2. 9 returns a sum but includes manually hidden rows and ignores filtered rows.

Steps

1. Select the cell where the result should appear.

2. Enter the formula:

   = \text{SUBTOTAL}(109, A1:A20)

3. Apply filters or hide rows.

4. View the updated total in the formula cell.

Windows and Mac differences

SUBTOTAL behaves the same on both Windows and Mac. No version differences affect this function.

2. Use the AGGREGATE function

The AGGREGATE function offers more control because it combines operation type and options in a single formula.

Function structure

\text{AGGREGATE}

(\text{function\_num}, \text{options}, \text{range})

Most used options for visible-only sums

1. 5 ignores hidden rows.

2. 7 ignores hidden rows and error values.

Steps

1. Select the target cell.

2. Enter the formula:

   $=\text{AGGREGATE}(9,5,A1:A20)$
   

3. Hide rows or filter the data.

4. View the total that changes according to visible values.

Windows and Mac differences

AGGREGATE works identically in Windows and Mac versions.

Does AutoSum sum only visible cells?

Yes, AutoSum works with SUBTOTAL in filtered lists. Excel inserts SUBTOTAL automatically when the AutoSum button is used inside a filtered column.

Steps

1. Filter your data.

2. Click the cell under the numeric column.

3. Select AutoSum on the ribbon.

4. Confirm the SUBTOTAL formula and press Enter.

How to sum selected visible cells only

To sum a non-contiguous set of visible cells:

Steps

1. Select the visible cells manually by holding Ctrl (Windows) or Command (Mac).

2. Look at the Status Bar at the bottom of Excel.

3. View the Sum indicator that shows the total for only the selected visible cells.

The status bar does not require formulas.

Common issues and solutions

The formula includes hidden cells

SUBTOTAL uses the wrong function number. Replace it with 109 to ignore all hidden rows.

Excel sums filtered but not hidden rows

AGGREGATE with option 5 ignores both manually hidden and filtered rows.

The total does not update after filtering

The formula references entire columns without proper ranges. Adjust the range to include only your dataset.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report