Excel’s AGGREGATE function lets you manage complex reports and ensure they foot correctly.

For example, suppose you are building a sales report by product and region.

Building the Grand Total calculation for a multi-level report like this is error-prone. You would need to use the SUM function and select every individual region while ensuring that no totals were included.

You can use instead use the AGGREGATE function to define all the totals … both the subtotal and the grand total. AGGREGATE can sum (among other things) and you can set the option to ignore other AGGREGATE formulas.

The AGREGATE function requires three parameters:

• A function number to define Average, Count, Max, Sum, etc.  Function number 9 is sum.
• An option to define how to interact with other subtotals, hidden rows, etc.  Option 0 ignores other subtotals and aggregate functions.
• The range to perform the function.

The image below shows the same report along with the formulas used.

Notice how the formula in C17 can include all the cells above it. You can now insert rows in any region and not have to update the grand total.

Additionally, you can have AGGREGATE ignore hidden rows. Hide a particular SKU, see the results and unhide the row. Very easy for on the fly what-if scenarios. The ignore hidden rows feature works very well when trying to derive a sum of an Autofiltered list.

Revelwood is an IBM Gold Business Partner with 25+ years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. Revelwood has helped clients in all sizes across all industries optimize and grow their use of Planning Analytics. Revelwood’s Planning Analytics team consists of experienced PA experts, including a multi-year IBM Champion.

Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Unpivot

IBM Planning Analytics Tips & Tricks: PAW Export Data

A Resolution You Should Actually Keep: Upgrade Your IBM Planning Analytics Environment