Sum Only Visible Task Counts After Filtering in Excel
How do I sum only visible task counts in Excel while excluding filtered-out rows and manually hidden rows?
Enter =SUBTOTAL(109,C2:C100) to add numeric task counts in visible rows. Excel excludes both rows removed by a filter and rows hidden manually.
| Row | A | B | C | Remove |
|---|---|---|---|---|
| 1 | Project | Status | Task count | |
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 | ||||
| 10 | Visible total | 22 |
- SUBTOTAL(109) on the visible rows
- 22
- What SUM(C2:C9) still says
- 44
How do I sum only visible task counts in Excel?
Use =SUBTOTAL(109,C2:C100) to add numeric task counts in the rows currently visible. Excel leaves out rows removed by a filter and rows hidden manually, as described in Microsoft’s SUBTOTAL guide.
=SUBTOTAL(109,C2:C100)
The 109 argument tells Excel to sum values while skipping manually hidden rows. Filtered-out rows are skipped too. Keep numeric task counts in C2:C100; task names and status labels do not belong in the summed range.
Place the formula outside the range, for example in C102. That keeps the total from being included in its own calculation. Unlike SUM, SUBTOTAL adjusts to row visibility. It does not specify a status, so filtering the table to Ready updates the total without changing the formula.
For other spreadsheet reporting guides, visit the spreadsheets and data topic page.
What sample data shows how the visible task-count total works?
When every row is visible, the eight task groups below total 44. Enter the 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 |
Row 1 holds the headers, and rows 2 through 9 hold the eight records. Each row is a group of tasks, not necessarily a single task. To verify the total before filtering, add the counts: 6 + 4 + 9 + 3 + 5 + 7 + 2 + 8 = 44.
Enter each count as a number. Excel will not treat text such as 6 tasks as a numeric count. The blank cells in C10:C100 have no effect, so the reference can include them until more records are added.
How do I set up and check the filtered total?
Turn on filtering for A1:C9, then put =SUBTOTAL(109,C2:C100) in C102. The result follows the documented SUBTOTAL rules and changes when rows enter or leave the visible view.
-
Enter the sample data. Put the headers in
A1:C1and the records inA2:C9. Column C needs numbers, not numeric text. -
Enable filtering. Select
A1:C9and choose Data > Filter. Limiting the filter to the source range keeps the summary area out of it. -
Label the total. Type
Visible task countinB102. This makes clear that the figure changes with the current view. -
Add the formula. Put
=SUBTOTAL(109,C2:C100)inC102. With every sample row visible, the expected total is44. -
Filter to Ready work. Open the filter menu in
B1, clear the other selections, and choose onlyReady. Moonberry, Starspoon, Waffleorbit, and Pebblekite should remain visible. -
Check the filtered total. The expected result is
22, from6 + 9 + 5 + 2. The Waiting and Blocked records remain in the worksheet, but the filter leaves their counts out of the total. -
Hide a visible record manually. Right-click worksheet row 6, where Waffleorbit setup appears, and choose Hide. Hide the entire row, not just its contents.
-
Check the combined result. The expected total becomes
17, from6 + 9 + 2. Waffleorbit’s five tasks are no longer included, and the filter still excludes the other records.
If the visible rows change but the total does not, check that calculation is set to automatic and recalculate the worksheet. To get back to 44, unhide row 6 and clear the status filter. Clearing the filter by itself does not reveal a manually hidden row.
What totals should Excel show for each sample worksheet view?
Expect 44 with all rows visible, 22 with the Ready filter on, and 17 when Ready row 6 is also hidden manually.
| 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 reveal row 6 while leaving the Ready filter active, type A6 in Excel’s Name Box and press Enter. Then choose Home > Format > Hide & Unhide > Unhide Rows.
To restore 44, make sure row 6 is visible and remove the status filter. Check both the filter and the row setting: removing the filter will not show a row hidden manually. A result of 0 means Excel counted no numeric values, or the values it counted added up to zero. It does not establish that the source contains no records.
Why does SUBTOTAL use 109 instead of 9 for hidden task rows?
Choose 109 to leave manually hidden rows out of the total; choose 9 to include them. Both numbers skip rows excluded by a filter, but only 109 skips rows hidden manually, according to Microsoft’s SUBTOTAL documentation.
| 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 gives 22, while 109 gives 17. I would use 9 if manual hiding is only a way to change the sheet’s appearance and hidden tasks still belong in the total.
These settings apply to rows, not columns. Hiding column C or using formatting to mask its values does not remove those values from the calculation. SUBTOTAL also ignores nested SUBTOTAL formulas in its reference, so it will not count those subtotals twice. Keep ordinary SUM formulas outside the source range.
How do I fix an incorrect visible task-count total?
Check the function number, the entries in the count column, the formula’s location, and the sizes of the formula and filter ranges. A mismatch often traces back to one of those worksheet details.
Hidden tasks still count. If the Ready filter is on, Waffleorbit is hidden, and the result is still 22, change =SUBTOTAL(9,C2:C100) to =SUBTOTAL(109,C2:C100). Make the same change if the current formula is =SUM(C2:C100). If you already use 109, confirm that worksheet row 6 is hidden. Hiding column C or clearing its cell text does not hide the row.
Visible counts are missing. Excel does not sum text stored in a visible count cell. Convert numeric text to numbers, and replace an entry such as 5 tasks with the numeric value 5. Applying Number format alone does not reliably turn text already in a cell into a number.
Excel reports a circular reference. Move the formula to C102 or another cell outside C2:C100. Do not put it in C100, which belongs to the summed range. Keep the summary outside the source range as records are added.
New records do not contribute. Extend both the formula reference and the worksheet filter range to include the new records. For example, change the formula to =SUBTOTAL(109,C2:C500) and expand the filter range as well. If you extend only the calculation reference, the new records remain outside the filter. For a tracker that grows often, an Excel Table with a structured column reference can extend the calculation reference when records are added.
A dashboard may need a calculation that does not depend on visible rows. For a related reporting problem, see how to calculate a dashboard completion rate without averaging percentages.
When should I use something other than a visibility-based task total?
Use another formula or report when the result needs to follow a fixed status, count individual task records, or compare categories rather than reflect the rows currently visible.
| 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 total tied to a fixed status should not change when someone hides a row. Use SUMIF or SUMIFS with a Ready status criterion if Ready tasks should count regardless of row visibility. Use SUBTOTAL when the figure should follow the current view.
For a count of visible records, use a column with one nonblank identifier per task. If an identifier is blank, SUBTOTAL(103,...) will undercount. If the report needs open tasks by owner instead, see Count Open Tasks by Owner with Google Sheets QUERY.