SUBTOTAL

Formulas / SUBTOTAL
To get a subtotal from a list or database.
=SUBTOTAL(function_num,ref1,[ref2],...)
  • function_num - specifies the function used for calculating subtotals in a list
  • ref1 - a named range or reference to subtotal
  • [ref2], ... - [OPTIONAL] additional named ranges or references to subtotal

Examples

    The SUBTOTAL function in Sourcetable can be used with various arguments to return different calculations. For example, the following formula uses the SUM function to return the sum of the visible cells in a range:

  • =SUBTOTAL(109,range)

    To return the count of visible cells in a range, the following formula can be used:

  • =SUBTOTAL(103,range)

    And to return the average of visible cells in a range, the following formula can be used:

  • =SUBTOTAL(101,range)

    In addition, the SUBTOTAL function can be used to only return the calculation for visible cells in the second argument. For example, the following formula uses the COUNT function to return the count of only visible cells in a range:

  • =SUBTOTAL(3,B7:B19)

    And the following formula uses the SUM function to return the sum of only visible cells in a range:

  • =SUBTOTAL(9,F7:F19)

Summary

The SUBTOTAL function is used to return a subtotal of a list or database.

  • The SUBTOTAL function calculates an aggregate result for supplied values. It can perform a variety of calculations, including SUM, AVERAGE, COUNT, and MAX.
  • By default, SUBTOTAL ignores values in hidden rows. However, it is possible to configure SUBTOTAL to include or exclude hidden values.
  • SUBTOTAL is useful to use with filtered data because it automatically ignores other SUBTOTAL formulas in references to prevent double-counting.

Frequently Asked Questions

What is the SUBTOTAL function?
The SUBTOTAL function aggregates a result for the values supplied.
What can SUBTOTAL return?
SUBTOTAL can return subtotals using SUM, AVERAGE, COUNT, MAX, and other functions.
Can SUBTOTAL include or exclude values in hidden rows?
Yes, SUBTOTAL can both include or exclude values in hidden rows.
Does SUBTOTAL exclude values in rows hidden with a filter by default?
Yes, SUBTOTAL excludes values in rows hidden with a filter by default, but can also include them with the appropriate function_num.
Does SUBTOTAL automatically ignore other SUBTOTAL formulas in references?
Yes, SUBTOTAL automatically ignores other SUBTOTAL formulas in references so it does not double count when calculating the subtotal.
What happens if the function_num is between 1-11?
If the function_num is between 1-11, SUBTOTAL will include manually hidden cells.
What happens if the function_num is between 101-111?
If the function_num is between 101-111, SUBTOTAL will exclude manually hidden cells.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV