Use =SUBTOTAL(109,$C$2:$C$500) to total only the rows left visible after you filter. SUM keeps adding hidden rows too, so a filtered list still shows the grand total, while SUBTOTAL recalculates every time you change the filter. Put the SUBTOTAL cell above the header row so the filter never hides it.
Why does SUM ignore my filter?
A filter only hides rows from view. The numbers are still in the cells, and SUM adds every cell in its range, visible or not. Say your expense log has 300 rows and you filter Category to Food. SUM of the amount column still shows the total of all 300 rows, perhaps ₹85,000, while only the Food rows are on screen. SUBTOTAL is designed to skip rows that a filter has removed.
How do I set it up?
- Keep column headings in row 3 with data from row 4, and leave rows 1 and 2 free.
- In C1 enter =SUBTOTAL(109,$C$4:$C$500) and label it in B1 as Total of visible rows.
- Select the headings and turn on Filter from the Data tab.
- Filter Category to Food. C1 now shows just the Food total, for example ₹14,400.
- Add a second filter for the month, and C1 narrows further.
If the formula sits below the data, a filter can hide it or the data range can grow into it. Above the headers is safer.
What is the difference between 9 and 109?
The first argument of SUBTOTAL says what to calculate. 9 and 109 both mean sum. The hundred series, 101 to 111, ignores rows you hid by hand as well as rows hidden by a filter. The one to eleven series ignores filtered rows only, and still counts rows you hid yourself with the Hide command. Filters are the same in both.
| Code | Calculation | Manually hidden rows |
|---|---|---|
| 9 | Sum | Included |
| 109 | Sum | Ignored |
| 3 | Count of non-empty cells | Included |
| 103 | Count of non-empty cells | Ignored |
| 1 | Average | Included |
| 101 | Average | Ignored |
For an expense log, 109 is usually what you want. You may also keep a count beside the total with =SUBTOTAL(103,$B$4:$B$500), so you see how many rows the total comes from, and an average with =SUBTOTAL(101,$C$4:$C$500).
Does SUBTOTAL work inside an Excel Table?
Yes. If you turn the log into a Table with Ctrl+T and switch on Total Row from the Table Design tab, Excel inserts a SUBTOTAL formula for you, using 109 for sums. The total row then follows the filter without you typing anything. The same total row can show a count or average instead.
Does it work in Google Sheets?
SUBTOTAL exists in Google Sheets with the same codes. How it treats rows hidden by a filter view and by a basic filter can differ from Excel, so test it on a few rows where you know the answer before you rely on it.
What if the filtered total looks wrong?
- Amounts stored as text are never added. Test with =ISNUMBER on one cell.
- The range ends before your last row. Use a generous end row such as 500, or a Table, which grows by itself.
- The filter was applied to a different range from the one the formula points at.
- A trailing space makes a category appear twice in the filter list, so one Food filter misses some rows. Look at the list of values to spot it.
When a sheet stops being enough
Filtering and totalling is what the Report builder in SpendixAI does, using filters and grouping on your saved transactions, with a CSV download if you want the filtered rows in a sheet.
Quick checklist
- Total cell above the headers.
- 109 for sum, 103 for count.
- Compare the filtered total with a hand total of three rows.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


