Skip to main content
Female viewing a tablet

How to Use Aggregate Function in Solver

In this tutorial with video, we show how to use the aggregate function in Solver with Excel. Learn how to run an expense report efficiently.
banner background

In database management, an aggregate function is a function where values of multiple rows are grouped together to form a single summary value. There are nearly 20 functions available to perform with the aggregate function in Excel. Common aggregate functions include:

  • Sum
  • Average
  • Median
  • Count
  • Standard deviation
  • Maximum
  • Minimum
  • Range

In this tutorial with video, we’ll show how to use the aggregate function in Solver with Excel to run an expense report efficiently.

Start by using Solver in design mode. Solver uses Excel as its Report Designer tool. In this example and shown in the video below, we open a basic financial statement and use the aggregate function to find total expenses.

Aggregate Versus Sum

The aggregate function gives you options that sum functionality does not. With the aggregate function, you can exclude values like nothing, error, etc.

Solver Excel aggregate functions

This functionality can be quite helpful when running reports that include multiple departments (or sections). Some departments may have expenses that others do not. In this scenario, the sum function may result in an error because particular values may not exist. By using an aggregate function instead, the report will only add the values that need to be summed.

Formula with Aggregate

Here’s the finished report using the aggregate function:

Report results

Short Video

Watch this short video to learn how to use the aggregate function in Solver.

The Business Technology Solutions Team at Forvis Mazars has certified experience with Solver and Microsoft business applications. We are a Microsoft Dynamics Gold Partner and 2022–23 recipient of the Microsoft Inner Circle award, in addition to the Solver Evangelist of the Year award. Connect with us and let us know how we may assist you with your reporting and planning needs and goals.

Related FORsights

Like what you see?
Subscribe to receive tailored insights directly to your inbox.