askvity

What is the formula for subtotal in Excel?

Published in Excel Formulas 3 mins read

The formula for subtotal in Excel is =SUBTOTAL(function_num, ref1, [ref2], ...). This formula calculates a subtotal for a list or database using various functions.

Understanding the SUBTOTAL Function

The SUBTOTAL function is versatile because it allows you to perform different calculations (like SUM, AVERAGE, COUNT) while ignoring hidden rows and other subtotals, preventing double-counting.

Components of the SUBTOTAL Formula:

  • function_num: This is a number between 1 and 11 (or 101 to 111) that specifies which function to use for the subtotal calculation.

    • 1-11: These numbers include hidden values in the calculation.
    • 101-111: These numbers exclude hidden values in the calculation.

    Here's a breakdown of some common function numbers as referenced from the Microsoft Support documentation:

    Function Number Function
    1 or 101 AVERAGE
    2 or 102 COUNT
    3 or 103 COUNTA
    4 or 104 MAX
    5 or 105 MIN
    9 or 109 SUM

  • ref1: This is the first range or reference for which you want to calculate the subtotal.

  • [ref2], ...: These are additional ranges or references that you can include in the subtotal calculation (optional).

Examples

Here are examples from the reference showing how to use the SUBTOTAL function.

  • =SUBTOTAL(9,A2:A5): This formula will sum the values in cells A2 to A5. The function number 9 specifies that the function is to be sum. In the example provided by Microsoft, the result is 303.
  • =SUBTOTAL(1,A2:A5): This formula calculates the average of the values in cells A2 to A5. The function number 1 specifies that the function is to be average. In the example provided by Microsoft, the result is 75.75.

Practical Insights:

  • Dynamic Data: SUBTOTAL is very useful when you use filters in Excel because it automatically recalculates based on the visible cells, ignoring the hidden ones.
  • Avoiding Double Counting: When used correctly, SUBTOTAL can help prevent double counting when you have existing subtotals in your data.
  • Function Numbers: Choosing between function numbers 1-11 and 101-111 is crucial for how your subtotals behave when rows are hidden using filter or grouping.
  • Multiple Ranges: You can add more ranges in the formula by separating each range with a comma.

The SUBTOTAL function is a powerful tool in Excel for dynamic calculations based on visible cells.

Related Articles