Does SUBTOTAL ignore filtered rows?

Yes. SUBTOTAL can calculate only visible rows when data is filtered.

What does 109 mean in SUBTOTAL?

109 tells SUBTOTAL to sum visible rows while ignoring manually hidden rows.

Why does my formula show an error?

Open the linked error pages (#N/A, #VALUE!, #REF!) from our directory. Most formula posts fail because of data type mismatches, deleted references, or wrong match modes — not because the function name is wrong.

Should I copy formulas down or use a Table?

Copying down is fine for one-off models. Convert the range to an Excel Table when you add rows often — structured references stay readable and break less.

Formulas - 2026-05-12

SUBTOTAL in Excel: Complete Guide

Learn SUBTOTAL in Excel for filtered lists, visible rows, sums, counts, averages, and report summaries that respond to filters.

What SUBTOTAL Does

SUBTOTAL calculates a summary such as sum, count, or average while respecting filtered rows. It is especially useful for filtered lists. Syntax: \\\ =SUBTOTAL(function_num, ref1) \\\

Sum Visible Rows

\\\ =SUBTOTAL(109,D2:D100) \\\ 109 means SUM while ignoring hidden rows.

Count Visible Rows

\\\ =SUBTOTAL(103,A2:A100) \\\ 103 counts visible non-empty cells.

Why Use SUBTOTAL Instead of SUM?

SUM includes hidden rows after filtering. SUBTOTAL can calculate only the visible records, making it better for filtered reports.

Related Guides

SUBTOTAL function How to Filter in Excel SUM function COUNTIFS in Excel

Formula debugging workflow

Step Action 1 Select the cell and read the formula in the formula bar 2 Use Formulas → Evaluate Formula for nested functions 3 Check for text stored as numbers (green triangle) 4 Fix errors using our formula error guide Go deeper: Compare similar functions · Fix common problems

Frequently asked questions

Does SUBTOTAL ignore filtered rows? Yes. SUBTOTAL can calculate only visible rows when data is filtered.
What does 109 mean in SUBTOTAL? 109 tells SUBTOTAL to sum visible rows while ignoring manually hidden rows.
Why does my formula show an error? Open the linked error pages (#N/A, #VALUE!, #REF!) from our directory. Most formula posts fail because of data type mismatches, deleted references, or wrong match modes — not because the function name is wrong.
Should I copy formulas down or use a Table? Copying down is fine for one-off models. Convert the range to an Excel Table when you add rows often — structured references stay readable and break less.