What's the difference between SUBTOTAL function numbers 1-11 and 101-111?

Numbers 1-11 include manually hidden rows in calculations, while 101-111 exclude them. In practice, use 101-111 when working with filtered data (AutoFilter) or when you want hidden rows ignored. Use 1-11 only when you specifically need to include manually hidden data in your calculations, which is rare in most business scenarios.

Can SUBTOTAL ignore other SUBTOTAL functions?

Yes, this is one of SUBTOTAL's greatest strengths. If you nest SUBTOTAL functions or have SUBTOTAL formulas within your range, the outer SUBTOTAL automatically ignores the inner ones, preventing double-counting. This makes it ideal for creating layered reports with subtotals at multiple levels.

Why should I use SUBTOTAL instead of SUM for filtered data?

SUM includes all cells in a range regardless of filtering status, while SUBTOTAL (with function numbers 101-111) only calculates visible cells. For accurate reporting on filtered datasets, SUBTOTAL is essential. Using SUM on filtered data will give misleading totals that include hidden rows you don't want counted.

Does SUBTOTAL work with Google Sheets and LibreOffice?

Yes, SUBTOTAL is available in both Google Sheets and LibreOffice Calc with identical syntax and function numbers. However, LibreOffice's implementation may have minor behavioral differences with certain function numbers. Always test your formulas in your target application to ensure consistent results.

How do I use SUBTOTAL with multiple non-adjacent ranges?

Simply add additional ref parameters separated by commas: =SUBTOTAL(109,A1:A10,C1:C10,E1:E10). This sums visible cells across all three ranges. You can include as many ranges as needed, making SUBTOTAL flexible for complex data layouts across your spreadsheet.

Excel SUBTOTAL Formula: Master Dynamic Calculations with Function Numbers

Intermediate

=SUBTOTAL(function_num, ref1, [ref2], ...)

The SUBTOTAL function is one of Excel's most powerful yet underutilized formulas, particularly valuable for financial analysts, accountants, and business professionals who work with filtered data. Unlike standard SUM or AVERAGE functions, SUBTOTAL intelligently handles hidden rows, making it the preferred choice for datasets that require frequent filtering or manipulation. This function becomes indispensable when you need to calculate subtotals on visible cells only, excluding manually hidden rows or rows filtered through AutoFilter. The SUBTOTAL formula uses a unique function number system (1-11 or 101-111) that determines which calculation to perform. The distinction between the two number ranges is crucial: numbers 1-11 include manually hidden rows in calculations, while 101-111 exclude them. This flexibility makes SUBTOTAL essential for creating professional financial reports, sales summaries, and analytical dashboards where data visibility directly impacts your results. Understanding when and how to use each function number will significantly enhance your Excel proficiency and data analysis capabilities.

Syntax & Parameters

The SUBTOTAL function follows this structure: =SUBTOTAL(function_num, ref1, [ref2], ...). The first parameter, function_num, is absolutely critical as it determines which mathematical operation to execute. Numbers 1-11 perform calculations while including manually hidden rows, whereas 101-111 perform the same operations while excluding hidden rows. Function numbers map as follows: 1 or 101 (AVERAGE), 2 or 102 (COUNT), 3 or 103 (COUNTA), 4 or 104 (MAX), 5 or 105 (MIN), 6 or 106 (PRODUCT), 7 or 107 (STDEV), 8 or 108 (STDEVP), 9 or 109 (SUM), 10 or 110 (VAR), and 11 or 111 (VARP). The ref1 parameter is your first data range—this could be a single cell, a named range, or a contiguous block of cells. You can include multiple ref parameters (ref2, ref3, etc.) to calculate across non-adjacent ranges, which is particularly useful when your data is scattered across different columns or sections. A critical advantage: SUBTOTAL ignores other SUBTOTAL functions within its ranges, preventing double-counting in nested scenarios. When working with filtered data in Excel, always use function numbers 101-111 to ensure hidden rows don't skew your calculations. This becomes especially important in financial reporting where accuracy is paramount.

function_num

Function number (1-11 or 101-111)

ref1

First reference

Practical Examples

Sales Report with AutoFilter

=SUBTOTAL(109,D2:D501)

Function number 109 performs SUM while excluding hidden rows. This ensures that only the visible Q4 sales rows are summed, giving an accurate quarterly total without manual row selection. If rows were hidden via AutoFilter, they're automatically excluded from the calculation.

Inventory Count with Multiple Ranges

=SUBTOTAL(103,B2:B100,D2:D100,F2:F100)

Function number 103 performs COUNTA (counts non-empty cells) while excluding manually hidden rows. By using multiple ref parameters, the formula counts all inventory items across three locations simultaneously. The use of 103 instead of 3 ensures hidden maintenance rows don't inflate the count.

Average Price Analysis with Filtered Data

=SUBTOTAL(101,C2:C500)

Function number 101 calculates AVERAGE while excluding hidden rows created by filtering. This is critical for accurate pricing analysis—if you used AVERAGE instead, hidden non-electronics rows would still be included, distorting the true average price of electronics.

Key Takeaways

SUBTOTAL is essential for accurate calculations on filtered data—use function numbers 101-111 to exclude hidden rows in AutoFilter scenarios
The function number determines both the calculation type (SUM, AVERAGE, COUNT, etc.) and whether hidden rows are included, making careful selection critical
SUBTOTAL automatically ignores nested SUBTOTAL functions, enabling professional multi-level reporting without double-counting concerns
Always use absolute references when copying SUBTOTAL formulas to prevent range shifts that create #REF! errors
SUBTOTAL is available across Excel, Google Sheets, and LibreOffice, though minor behavioral differences may exist—always test in your target platform

Pro Tips

Always use function numbers 101-111 when working with AutoFilter. This ensures your calculations reflect only the visible, filtered data—the most common business requirement.

Impact : Prevents reporting errors and ensures stakeholders see accurate figures that match their filtered view of the data.

Use absolute references ($D$2:$D$501) instead of relative references when building SUBTOTAL formulas you'll copy across rows or columns. This prevents reference shifts that create hard-to-debug errors.

Impact : Saves debugging time and ensures formulas work correctly when copied to other cells or worksheets.

Leverage SUBTOTAL's ability to ignore other SUBTOTAL functions when creating multi-level reports. Place subtotals at the detail level and summary SUBTOTAL formulas at the aggregate level without double-counting.

Impact : Enables professional hierarchical reporting structures (by region, then by country, then by product) without complex workarounds.

Test your SUBTOTAL formulas with data filtered both ways (showing and hiding rows) to verify they behave as expected. Different function numbers (1-11 vs 101-111) produce different results with hidden rows.

Impact : Catches logic errors before deploying reports to stakeholders, ensuring reliability and credibility of your analysis.

Useful Combinations

SUBTOTAL with IF for Conditional Analysis

=SUBTOTAL(109,IF(B2:B100>1000,C2:C100,0))

Combine SUBTOTAL with IF to sum values only when a condition is met. This array formula sums values in column C only where column B exceeds 1000, while still respecting filtered rows. Enter as array formula in Excel 2019 and earlier using Ctrl+Shift+Enter.

SUBTOTAL with IFERROR for Error Handling

=IFERROR(SUBTOTAL(101,A1:A100),"No data available")

Wrap SUBTOTAL in IFERROR to gracefully handle cases where all rows are filtered out or the range contains no numeric values. This prevents #DIV/0! errors and displays a user-friendly message instead, improving report readability.

SUBTOTAL in Dynamic Dashboard with Helper Columns

=SUBTOTAL(109,OFFSET($A$1,0,COLUMN()-1,ROWS($A$2:$A$100),1))

Combine SUBTOTAL with OFFSET to create dynamic calculations that adjust based on column position. This advanced technique enables flexible dashboards where filtering or column changes automatically update your subtotals without manual formula adjustment.

Common Errors

#VALUE!

Cause: The function_num parameter is invalid or outside the range 1-11 or 101-111. For example, using =SUBTOTAL(50, A1:A10) or =SUBTOTAL("SUM", A1:A10) instead of numeric function numbers.

Solution: Verify you're using only valid function numbers (1-11 or 101-111). Use 109 for SUM, 101 for AVERAGE, etc. Never use text function names like "SUM"—SUBTOTAL requires numeric codes only.

#REF!

Cause: The reference range (ref1 or subsequent ref parameters) contains invalid cell references, such as deleted columns, incorrect range syntax, or circular references. This often occurs when copying SUBTOTAL formulas and the referenced range shifts unexpectedly.

Solution: Use absolute references with dollar signs (e.g., =SUBTOTAL(109,$D$2:$D$501)) to prevent range shifts when copying. Verify all referenced ranges exist and contain valid cell addresses.

#DIV/0!

Cause: When using SUBTOTAL with function numbers 1, 7, 8, 10, or 11 (AVERAGE, STDEV, STDEVP, VAR, VARP), all visible cells in the range are empty or filtered out, creating a division by zero error.

Solution: Ensure your filtered data contains at least one numeric value. Use IFERROR to handle this gracefully: =IFERROR(SUBTOTAL(101,A1:A100),0) to return 0 instead of an error when no data is available.

Troubleshooting Checklist

1.Verify the function_num is between 1-11 or 101-111; check for typos or invalid numbers that cause #VALUE! errors
2.Confirm your ref1 range contains valid cell addresses and hasn't shifted due to deleted columns or rows causing #REF! errors
3.Check whether you need 101-111 (exclude hidden rows) or 1-11 (include hidden rows) based on your filtering requirements
4.Ensure the range contains appropriate data types for your function number (numeric values for SUM/AVERAGE, any values for COUNT)
5.Test with AutoFilter applied and removed to verify the formula produces expected results in both scenarios
6.If getting #DIV/0!, verify the range contains numeric values and at least one row is visible after filtering

Edge Cases

All rows in the range are hidden via AutoFilter

Behavior: SUBTOTAL with function numbers 101-111 returns 0 for SUM, and #DIV/0! for AVERAGE, STDEV, VAR, and similar functions that require at least one value

Solution: Wrap in IFERROR: =IFERROR(SUBTOTAL(101,A1:A100),0) to return 0 instead of an error

This is expected behavior—no visible data means no calculation possible for some functions

Range contains text mixed with numbers, using function number 109 (SUM)

Behavior: SUBTOTAL ignores text values and sums only numeric cells, similar to standard SUM behavior

Solution: No action needed—SUBTOTAL handles this gracefully by default

This is beneficial for mixed-type data; however, COUNTA (103) will count both text and numbers

Using SUBTOTAL with entire column references like A:A instead of specific ranges

Behavior: Excel may slow down significantly or display performance issues due to processing the entire column including headers and empty cells

Solution: Always specify exact ranges: =SUBTOTAL(109,A2:A1000) instead of =SUBTOTAL(109,A:A)

Best practice for performance optimization, especially in large workbooks with multiple SUBTOTAL formulas

Limitations

•SUBTOTAL cannot ignore errors within the range (#DIV/0!, #N/A, etc.)—use AGGREGATE function instead if error-ignoring is required
•SUBTOTAL function numbers 1-11 include manually hidden rows, which is rarely desired in modern filtered-data scenarios; requires careful function number selection to avoid mistakes
•SUBTOTAL cannot perform calculations based on multiple criteria the way SUMIFS can—for complex conditional logic, combine with IF arrays or use AGGREGATE instead
•Performance degrades with very large ranges or when using multiple non-adjacent ref parameters; for datasets exceeding 100,000 rows, consider using Pivot Tables for better performance

Alternatives

AGGREGATE Function

AGGREGATE offers more function options (19 functions) and additional error-handling capabilities. It can ignore error values (#DIV/0!, #N/A, etc.) within ranges, which SUBTOTAL cannot do.

When: Use AGGREGATE when you need to ignore errors in your data or require functions beyond SUBTOTAL's 11 options, such as LARGE or SMALL. AGGREGATE is more powerful but also more complex.

SUM with IF Array Formula

Provides granular control over which rows to include based on custom criteria. You can create complex conditional logic that SUBTOTAL cannot match.

When: Use when you need to sum based on multiple conditions (e.g., sum sales only for products over $100 AND in the Electronics category). Requires array formula entry (Ctrl+Shift+Enter in older Excel versions).

SUMIF or SUMIFS

Designed specifically for conditional summation based on criteria. Simpler syntax than array formulas and easier to read for other users.

When: Use when you need to sum based on a single criterion (SUMIF) or multiple criteria (SUMIFS). However, these don't respect AutoFilter like SUBTOTAL does.

Compatibility

✓ Excel

Since 2007

=SUBTOTAL(function_num, ref1, [ref2], ...) with function numbers 1-11 or 101-111

✓Google Sheets

=SUBTOTAL(function_num, range1, [range2], ...) - identical to Excel

Google Sheets supports all function numbers 1-11 and 101-111. Behavior with filtered data is consistent with Excel's 101-111 range. Works reliably with Google Sheets' filter functionality.

✓LibreOffice

=SUBTOTAL(function_num, range1, [range2], ...) - identical to Excel

Frequently Asked Questions

Master advanced Excel formulas like SUBTOTAL with ElyxAI's interactive tutorials and real-world examples. Elevate your data analysis skills and create professional reports with confidence using our comprehensive Excel training platform.

AGGREGATE

ACOSH