Last updated July 2026. SUBTOTAL totals only the rows a filter is currently showing: =SUBTOTAL(9,C2:C500) sums column C and recalculates every time you change the filter. The syntax is =SUBTOTAL(function_num, ref1, ...), where function_num picks the operation, and 9 means SUM. Use 109 instead when you also want it to ignore rows you hid by hand. SUBTOTAL also skips any other SUBTOTAL formulas inside the range, which is what keeps grouped totals from double counting.
This is the function that makes a converted transaction list actually answerable. Filter a statement to one vendor and a plain SUM still shows the whole column, because SUM does not care what is hidden. SUBTOTAL responds to the filter, so the number at the bottom of the sheet always matches what you are looking at. Below: the function numbers, the hidden row rule, the Data Subtotal command that shares the name, and the reasons it comes back wrong.
How do I use SUBTOTAL in Excel?
Put =SUBTOTAL(9,C2:C500) in a cell above or below your data, not inside the filtered range, and turn on AutoFilter with Ctrl+Shift+L. As you filter to a vendor, a month, or an amount range, the SUBTOTAL result updates to the sum of just those visible rows. Excel writes the same formula for you if you click AutoSum on a list that is already filtered, which is a quick way to see the pattern.
Keep the formula outside the filtered rows. If it sits in a row the filter can hide, the answer disappears along with the data. Above the header or two rows below the last record are both safe places.
What do the SUBTOTAL function numbers mean?
The first argument selects the calculation. There are two sets of the same eleven operations, and the difference between them is only about manually hidden rows.
| Includes hidden rows | Ignores hidden rows | Function | Typical use |
|---|---|---|---|
| 1 | 101 | AVERAGE | Average transaction size in the current view |
| 2 | 102 | COUNT | How many numeric entries are showing |
| 3 | 103 | COUNTA | How many rows are showing at all |
| 4 | 104 | MAX | Largest visible amount |
| 5 | 105 | MIN | Smallest visible amount |
| 9 | 109 | SUM | Total of the filtered rows |
| 6, 7, 8, 10, 11 | 106, 107, 108, 110, 111 | PRODUCT, STDEV, STDEVP, VAR, VARP | Statistical work on a filtered set |
Nine and 109 are the two you will use most. Three, or 103, is the other genuinely useful one: it counts the rows currently showing, which is how you report that 42 of 1,180 transactions matched your filter without counting them by eye.
What is the difference between SUBTOTAL 9 and 109?
Both ignore rows removed by a filter. The difference is rows you hid yourself with right click and Hide: 9 still includes those values, while 109 leaves them out. Microsoft's documentation puts it plainly, with 1 to 11 including manually hidden rows and 101 to 111 excluding them, and filtered rows excluded either way.
In practice, use 109 when you want the total to reflect exactly what is on screen no matter how a row got hidden, and 9 when hiding a row is a display choice that should not change the arithmetic. If a total ever looks wrong by exactly the value of a row you hid last week, this is the reason.
Why does SUBTOTAL ignore other subtotals?
By design, so grouped reports add up correctly. If your range already contains SUBTOTAL formulas, for example a total under each vendor block, a grand total written with SUBTOTAL skips those inner results and adds only the underlying rows. Write the grand total with plain SUM instead and you get roughly double the real figure, because SUM counts both the detail lines and each group total.
Two other design notes matter. SUBTOTAL is built for vertical ranges, so hidden columns never affect the result, only hidden rows. And it works on a single sheet, not across a 3D reference spanning several sheets.
SUBTOTAL vs SUM vs Data Subtotal
Three things share the name and do different jobs. Picking the wrong one is the most common source of confusion here.
| Tool | What it does | Use it when |
|---|---|---|
=SUM() | Totals every cell in the range, filtered or not | You want the whole population, always |
=SUBTOTAL(9,...) | Totals only the rows the filter is showing | You are exploring, sampling, or reviewing a slice |
| Data > Subtotal | Inserts grouped total rows into the data itself | You want a printed report broken by category |
=AGGREGATE() | Like SUBTOTAL but can also skip error values | The column still contains #N/A or #DIV/0! |
The Data Subtotal command is worth knowing because it is fast, but it changes your data layout. Sort by the column you want to group on first, then Data > Subtotal, choose the grouping column and the function, and Excel inserts a total row after each group along with an outline you can collapse. To reverse it, open the same dialog and click Remove All. Do not run it on a range you are about to feed into a pivot table, because the inserted total rows become records in the pivot.
How do I count visible rows after filtering?
Use =SUBTOTAL(103,B2:B500) against a column that is populated on every row, such as the date or the description. COUNTA counts non empty cells, so pointing it at a column with gaps undercounts the rows. The result updates with each filter change, which gives you a live record count next to your live total.
That pair, a visible count and a visible sum, is the fastest sanity check there is on converted data. Filter to one month: does the count look like a month of activity, and does the total agree with the closing figure on the source document? If either is off, you have found a problem before it reaches a report.
Why is SUBTOTAL returning 0 or the wrong number?
Nearly always one of four causes, and all four are quick to check.
| Symptom | Cause | Fix |
|---|---|---|
| Returns 0 with obvious numbers on screen | The amounts are text, not numbers | Convert the column to real numbers, then retotal |
| Total does not change when you filter | The formula uses SUM, not SUBTOTAL | Swap in =SUBTOTAL(9,...) |
| Total is roughly double | Plain SUM over a range containing group totals | Use SUBTOTAL, which skips nested subtotals |
| Hidden rows still counted | Function number 9 instead of 109 | Switch to the 101 to 111 set |
The first line is the one that bites after a conversion. Amounts that came out of a PDF as text look perfectly normal, sit left aligned in the cell, and quietly total to zero. Fixing the types is covered in the guide to converting text to numbers in Excel, and a stray space in the value causes the same silent failure, which the notes on removing spaces handle.
Using SUBTOTAL on converted statements and reports
Here is the workflow that gets the most out of it. Convert the PDF, put the data in one flat table with a header row, and add two cells above the header: =SUBTOTAL(9,D2:D5000) for the amount column and =SUBTOTAL(103,A2:A5000) for the count. Turn on AutoFilter. Now every question is a filter away, and the answer is already on screen.
Filter to one vendor to see what you paid them this year. Filter to a date range to tie a period back to the statement. Filter to amounts above your review threshold to see how many items and how much money that covers. This is also how you validate an extraction: filter to blanks in the amount column, and if any rows show up, the conversion missed something on that page. Working with financial records that arrive as statements each month, turning them into clean spreadsheet rows first is what makes this whole approach practical.
When the questions get repetitive, graduate from filters to a pivot table, which answers all of them at once instead of one at a time. Until then, SUBTOTAL plus a filter is faster to set up and easier to check. The companion guides on filtering data in Excel, summing a column, and building a pivot table cover the next steps, and you can convert a statement to a spreadsheet with the PDF to Excel converter to try it on your own data.
Can I use SUBTOTAL inside an Excel table?
Yes, and the table version is easier. Select a cell in the table, go to Table Design, and tick Total Row. Excel adds a row at the bottom and fills it with SUBTOTAL formulas, with a dropdown in each cell so you can switch between Sum, Average, Count, Max, and Min without writing anything. Because they are SUBTOTAL formulas, the totals respect the table's filters automatically.
The one thing to watch is that a table Total Row uses structured references like =SUBTOTAL(109,[Amount]), which expand as the table grows. That is usually what you want. If you need a total that stays anchored to a fixed range regardless of new rows, write the formula yourself with ordinary cell references.