Alternative Ways to Sum Data in Excel (SUM vs. SUBTOTAL)

While SUM is the workhorse for basic addition in Excel, SUBTOTAL offers a unique functionality for handling filtered or hidden data. Here's a breakdown of both:

SUM:

* Simplest and most common method.
* Adds all the values in a specified range (e.g., =SUM(A1:A10)).
* Includes filtered data: If you filter your data, SUM will consider all visible rows in the calculation.
* Includes hidden rows: SUM will add values from hidden rows as well.

SUBTOTAL:

* Ideal for working with filtered or hidden data.
* Offers various options depending on the first argument (number) within the function.
* 1 (default): Excludes filtered data but includes hidden rows.
* 109: Excludes filtered and hidden rows.
* Other codes (101, 103, etc.) handle different combinations of filtering and hidden rows.
* Syntax: =SUBTOTAL(number, range)

Choosing Between SUM and SUBTOTAL:

* Use SUM for straightforward addition of all data in a range, regardless of filters or hidden rows.
* Use SUBTOTAL when you want the sum to reflect only the visible data after filtering or exclude hidden rows from the calculation.

Additional Options for Summing Data:

* SUMIFS: Adds values based on one or more criteria (e.g., =SUMIFS(A1:A10, B1:B10, ">50")).
* SUMPRODUCT: Multiplies corresponding cells in two or more ranges and then sums the products (advanced use case).
* AutoSum: A quick way to insert SUM formulas. Select a cell below your data range and click the AutoSum button (Σ) on the ribbon. Excel will automatically suggest the appropriate SUM formula for your data.

Remember: The best method depends on your specific needs and how you want the filtering and hidden rows to affect your calculations.