How do I sum only visible task counts in Excel while excluding filtered-out rows and manually hidden rows?

In Excel, use =SUBTOTAL(109,C2:C100) to sum numeric task counts in visible rows while excluding both filtered-out rows and manually hidden rows. Place the formula outside its source range, such as in C102, so the total reflects the current worksheet view without creating a circular reference.

Sum Only Visible Task Counts After Filtering in Excel

Which Excel formula sums task counts while excluding filtered-out and manually hidden rows?

Excel’s =SUBTOTAL(109,C2:C100) sums numeric task counts in visible rows, excluding both filtered-out rows and manually hidden rows.

=SUBTOTAL(109,C2:C100)

Function number 109 selects a sum that ignores manually hidden rows; filtered-out rows are also excluded. The range C2:C100 must contain task counts, not task names or status labels.

Place the formula in C102, outside its source range. A regular SUM includes values regardless of row visibility, but this SUBTOTAL result follows the current view. Changing a filter changes which counts contribute; the formula does not permanently select a particular status.

What sample data shows how the visible task-count total works?

The following Excel tracker contains eight task groups whose numeric counts total 44 when all rows are visible.

Enter this sample in A1:C9. The project names are fictional.

Project	Status	Task count
Moonberry onboarding	Ready	6
Cloudpickle cleanup	Waiting	4
Starspoon documentation	Ready	9
Pocketcomet review	Blocked	3
Waffleorbit setup	Ready	5
Fizzfern migration	Waiting	7
Pebblekite handoff	Ready	2
Noodlenova planning	Blocked	8

Headers occupy row 1; Moonberry starts in row 2, and Noodlenova ends in row 9. Each record represents a group of tasks, not necessarily one task.

Enter counts as numbers alone. Text such as 6 tasks will not contribute to the sum. Blank cells in C10:C100 contribute nothing, so those rows can remain in the formula before containing data.

How do I set up and filter the visible task-count total in Excel?

To set up the visible task-count total in Excel, enable filtering on A1:C9 and enter =SUBTOTAL(109,C2:C100) in C102.

Enter the sample data. Put headers in A1:C1 and records in A2:C9. Confirm that column C contains numbers, not numeric text.
Enable filtering. Select A1:C9, then choose Data > Filter. Select the source explicitly so the summary area is not included.
Label the total. Enter Visible task count in B102 to identify the result as dependent on the current view.
Add the formula. Enter =SUBTOTAL(109,C2:C100) in C102. With all sample rows visible, the expected result is 44.
Filter to Ready work. Open the filter menu in B1, clear the other selections, and select only Ready. Moonberry, Starspoon, Waffleorbit, and Pebblekite should remain visible.
Check the filtered total. Cell C102 should display 22, from counts 6, 9, 5, and 2. Waiting and Blocked records remain stored but do not contribute.
Hide a visible record manually. Right-click worksheet row 6, containing Waffleorbit setup, and choose Hide. Hide the entire row, not its contents.
Check the combined result. The expected total is 17, representing 6 + 9 + 2. Waffleorbit’s five tasks are now excluded alongside the filtered-out records.

If a visibility change leaves the result unchanged, select Formulas > Calculation Options > Automatic, then Formulas > Calculate Now.

What totals should Excel show for each sample worksheet view?

For the sample tracker, =SUBTOTAL(109,C2:C100) should return 44 for all rows, 22 for Ready rows, and 17 when Ready row 6 is also manually hidden.

Worksheet view	Counts included	Expected result
All sample rows visible	All eight task counts	44
Status filtered to Ready	6, 9, 5, 2	22
Ready filter active, row 6 manually hidden	6, 9, 2	17
Ready filter active, row 6 restored	6, 9, 5, 2	22

To restore row 6 while retaining the Ready filter, enter A6 in Excel’s Name Box, press Enter, then choose Home > Format > Hide & Unhide > Unhide Rows.

To restore 44, ensure row 6 is unhidden and clear the status filter. Check both conditions rather than assuming clearing filter criteria restores every manually hidden row.

A result of 0 means no numeric values contribute, or contributing values total zero. It does not prove the source contains no records.

Why does SUBTOTAL with function number 109 sum only visible task counts?

SUBTOTAL(109,C2:C100) sums only visible task counts because 109 selects addition while excluding both filtered-out and manually hidden rows. Excel interprets SUBTOTAL as a calculation that responds to row visibility, making it suitable for a current-view total rather than the visibility-independent total produced by SUM.

The first argument, 109, specifies both the operation and the treatment of manually hidden rows. It selects a sum, like 9, but additionally omits rows hidden manually. Both choices exclude rows removed from view by filtering.

The second argument, C2:C100, supplies the task counts to examine. Summing this column measures tasks across groups rather than counting group records. Excel adds the eligible numeric values and displays the result in C102, outside the source range.

With the sample data, all eight counts contribute 44. Filtering to Ready leaves 6, 9, 5, and 2, totaling 22. Hiding row 6 removes the contribution of 5, producing 17 without changing the stored counts.

Why does SUBTOTAL use 109 instead of 9 for hidden task rows?

Excel’s SUBTOTAL(109,...) excludes manually hidden rows, while SUBTOTAL(9,...) includes them; both exclude filtered-out rows.

Formula	Excludes filtered-out rows	Excludes manually hidden rows
`=SUBTOTAL(9,C2:C100)`	Yes	No
`=SUBTOTAL(109,C2:C100)`	Yes	Yes

With the Ready filter active and Waffleorbit hidden, function number 9 returns 22; 109 returns 17. Choose 9 if manual hiding is cosmetic and hidden tasks should still count.

Visibility applies to rows, not columns. Hiding column C or concealing values through formatting does not exclude those values.

SUBTOTAL also ignores nested SUBTOTAL formulas within its reference, avoiding double-counting those subtotals. Ordinary SUM formulas are not automatically excluded, so keep them outside the source range.

How do I fix incorrect visible task-count totals in Excel?

Incorrect Excel visible task-count totals commonly require correcting the function number, converting text to numbers, moving the total outside its range, or expanding both calculation and filter ranges.

Hidden tasks still count

Symptom: With Ready selected and Waffleorbit hidden, the total remains 22.

Exact fix: Replace =SUBTOTAL(9,C2:C100) with =SUBTOTAL(109,C2:C100). If the formula is =SUM(C2:C100), replace that too.

If 109 is already present, confirm worksheet row 6 is actually hidden. Clearing text or hiding column C does not exclude the row.

Visible counts are missing

Symptom: A visible count does not contribute.

Exact fix: Select affected cells and choose Convert to Number from any number-stored-as-text warning. For plain numeric text without that warning, use Data > Text to Columns > Finish, with the column format set to General.

Replace 5 tasks with numeric 5. Applying Number format alone does not reliably convert existing text.

Excel reports a circular reference

Symptom: Excel warns about a circular reference or the total calculates incorrectly.

Exact fix: Move the formula to C102 or another cell outside C2:C100. Do not place it in C100, which belongs to the summed range. Keep the summary outside later range expansions too.

Newly added tasks do not contribute

Symptom: A visible record below row 100 is missing from the result.

Exact fix: Move the summary to E1, which filters can’t hide because row 1 holds the headers, clear the old total from column C, and expand the formula, for example to =SUBTOTAL(109,C2:C500).

Expand the filter range too; extending the calculation reference does not extend filtering. For frequent growth, use Insert > Table and a structured column reference that expands with added records.

When should I use something other than a visibility-based task total?

Use a method other than SUBTOTAL(109,...) when task totals must follow fixed criteria, count individual records, or compare categories rather than reflect row visibility.

Reporting need	More suitable method
Always total Ready tasks	`SUMIF` or `SUMIFS` with a status criterion
Count one task per row	`SUBTOTAL(103,...)` on task identifiers
Compare totals across statuses	PivotTable with task count summarized by Sum

A fixed-status calculation should not change merely because someone hides a row. SUMIF and SUMIFS express that status rule independently of visibility.

For visible record counting, choose a column containing one nonblank identifier per task. Blank identifiers cause SUBTOTAL(103,...) to undercount.

A PivotTable presents category totals together instead of requiring repeated worksheet filter changes. Worksheet row visibility is not the rule for those category totals.