Ravindra BagaleCourses & study guides Track your progress

Guides

SUBTOTAL and AGGREGATE: Totals That Ignore Hidden Rows and Errors

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Zoya filters her sales list to Pune and the SUM at the bottom still shows the total for all cities. Then one cell shows #N/A from a lookup, and the whole total turns into #N/A. SUBTOTAL fixes the first problem and AGGREGATE fixes the second.

=SUBTOTAL(9,range) sums only the rows that are visible after a filter. =SUBTOTAL(109,range) also skips rows you hid by hand. =AGGREGATE(9,6,range) sums while ignoring error values, and option 7 ignores both hidden rows and errors.

Download the practice file (subtotal-aggregate.xlsx)

Excel file, 6 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

  1. Filtered lists: use =SUBTOTAL(9,C2:C9) instead of =SUM(C2:C9).
  2. If you also hide rows by hand (right-click > Hide), use =SUBTOTAL(109,C2:C9).
  3. If the range may contain errors, use =AGGREGATE(9,6,C2:C9).
  4. For hidden rows and errors together: =AGGREGATE(9,7,C2:C9).
  5. In an Excel Table, the Total Row writes SUBTOTAL(109,...) for you.
=SUBTOTAL(9,C2:C9)
=SUBTOTAL(109,C2:C9)
=AGGREGATE(9,6,C2:C9)

Function numbers you need

JobSUBTOTAL (keeps hand-hidden rows)SUBTOTAL (skips hand-hidden rows)AGGREGATE
Sum91099
Count numbers21022
Count non-blank31033
Average11011
Max / Min4 / 5104 / 1054 / 5

Both SUBTOTAL versions always skip rows hidden by a filter. The only difference is rows hidden by hand.

The practice sheet

Rows 4 and 5 (Nashik) are hidden by hand, and Nagpur Aug (C7) holds an error, #N/A, from a lookup. The results:

FormulaResultWhy
=SUM(C2:C9)#N/AOne error spoils SUM
=AGGREGATE(9,6,C2:C9)₹6,61,000Ignores the error, counts hidden rows
=AGGREGATE(9,7,C2:C9)₹4,88,000Ignores the error and the hidden Nashik rows
=SUBTOTAL(109,D2:D9)₹4,88,000Column D is the same data with the error replaced by 0
=SUBTOTAL(9,D2:D9)₹6,61,000Code 9 keeps hand-hidden rows

Where these help

  • A total row above a filtered list that changes with the filter.
  • Reports where you hide detail rows to show a summary.
  • Data with a few lookup errors you cannot fix yet (better still: fix them with IFNA).
  • Data > Subtotal (the feature) inserts SUBTOTAL(9,...) rows per group, and its Grand Total does not double count them.

Ravindra Bagale's Tip

Put the SUBTOTAL total above the header row of a filtered list, not below it. A total below the data can disappear when filters hide rows near the bottom, and it gets in the way when new rows are added.

Practice task

  1. Open the Sales sheet. Rows 4 and 5 are hidden; C7 shows #N/A.
  2. Check F2 (SUM) shows #N/A and F3 (AGGREGATE 9,6) shows a total.
  3. Unhide rows 4 and 5 (select rows 3 to 6, right-click > Unhide) and watch F4 and F5 change.
  4. Add a filter (Ctrl + Shift + L) and show only Pune. Check F5 changes.
  5. Replace C7's error with =IFNA(NA(),0) and see SUM work again.

Learn it properly

Course lessons:

Related guides: IFERROR vs IFNA in Excel: Replace #N/A and #DIV/0! the Right Way · Excel Table vs Normal Range: When to Use Ctrl + T · Sort by Several Columns, Custom Sort Order and Advanced Filter · SUMIF and SUMIFS

Frequently asked questions

What is the difference between SUBTOTAL 9 and 109?

Both ignore rows hidden by a filter. 109 also ignores rows you hid manually; 9 still includes them.

Does SUBTOTAL ignore other SUBTOTALs?

Yes. SUBTOTAL skips cells that contain SUBTOTAL or AGGREGATE formulas, so group totals are not counted twice.

Which Excel versions have AGGREGATE?

Excel 2010 and later.

Can SUBTOTAL ignore errors?

No. Use AGGREGATE with option 6 or 7.