The regular Excel SUM formula calculates the total of all values in a range, including those hidden by filters or the "Hide" feature. While this default behavior is suitable for many scenarios, it can become problematic when creating interactive spreadsheets that require dynamic calculations based on filtered data. Fortunately, the SUBTOTAL function offers a solution to this challenge.
By using the SUBTOTAL function, you can perform calculations on filtered data without including hidden values. This function is designed to work specifically with filtered ranges, making it an essential tool for creating interactive spreadsheets that respond accurately to user inputs and filters.
When using the SUBTOTAL function, you have the flexibility to choose from a range of aggregation options, including Excel SUM, AVERAGE, COUNT formulas and more. This allows you to customize your calculations based on your specific requirements, whether you're summing a column, adding columns, or performing other aggregate calculations in Excel.
In summary, the SUBTOTAL function in Excel provides a powerful way to accurately sum columns and perform other aggregate calculations on filtered data, making it an invaluable tool for creating dynamic and interactive spreadsheets.
Subtotal Function: Syntax
=SUBTOTAL(9, range to sum) (to not ignore hidden cells)
=SUBTOTAL(109, RANGE TO SUM) (to not ignore hidden cells)
Subtotal Function: Steps
- Begin by typing in =SUBTOTAL(
- Continue with 9, or 109,
- Select or typing the range reference that contains the range to be added (i.e. H3:H10)
- Finish the formula by typing in ) and press Enter
How
The SUBTOTAL function in Excel is a versatile tool that combines the functionality of 11 separate functions into one. In this article, we'll focus on its ability to handle filtered and manually hidden cells, which is particularly useful for creating dynamic and interactive spreadsheets.
When using the SUBTOTAL function, you need to provide two arguments. The first argument specifies the function's behavior, such as Excel SUM formula, and the second argument defines the range of cells to be evaluated. For example, to perform a sum operation, you can use either the number 9 or 109 as the first argument, with the former ignoring manually hidden cells and the latter including them.
Once you've defined the arguments, select the range of cells you want to include in the sum, similar to how you would with a regular Excel SUM formula. The SUBTOTAL function will then calculate the sum based on the specified range and function type, considering any filters or manual hiding of cells.
By leveraging the SUBTOTAL function's capabilities, you can ensure that your Excel calculations accurately reflect the visible data in your spreadsheets, even when filters or manual hiding are applied. This functionality is crucial for creating robust and user-friendly Excel models that respond dynamically to changes in data visibility.
=SUBTOTAL(9,H3:H10)
=SUBTOTAL(109,H3:H10)
