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

`=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)`

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:

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.

The SUBTOTAL function aggregates a result for the values supplied.

SUBTOTAL can return subtotals using SUM, AVERAGE, COUNT, MAX, and other functions.

Yes, SUBTOTAL can both include or exclude values in hidden rows.

Yes, SUBTOTAL excludes values in rows hidden with a filter by default, but can also include them with the appropriate function_num.

Yes, SUBTOTAL automatically ignores other SUBTOTAL formulas in references so it does not double count when calculating the subtotal.

If the function_num is between 1-11, SUBTOTAL will include manually hidden cells.

If the function_num is between 101-111, SUBTOTAL will exclude manually hidden cells.

Drop CSV