MM_GROUPBY function groups all values based on keys and and applies a grouping function (SUM, AVG, MIN, MAX).

Syntax

=MM_GROUPBY(Keys, Values, Type, Name, Index)

• Keys

  Keys range based on which we perform grouping.

• Values

  Values range that we group based on grouping type.

• Type

  String.

  Type of grouping (SUM, AVG, MIN, MAX) for values. Default value is SUM.

• "Name", Index (optional)

  String, Integer.

  Name and dimension index of the first loop you want to return. If you want to access a specific number of simulations, use Name = "SIM".

• "Name2", Index2 (optional)

  String, Integer.

  Name and dimension index of the second loop you want to return. If you want to access a specific number of simulations, use Name = "SIM".

  Warning: The parameters Type, Name, Name2, ... are case sensitive

Remarks

• This function is not meant to be used inside another function. It must be used alone in a cell.

• Keys range and Values range must have the same number of rows in function MM_GROUPBY.

• If Keys include loop dimension, loop must be the same row size than loop size or number of rows from Values range.

• You can include loops to keys and/or values range.

• The groupby grid can not have several dimensions. It has either the specified dimension mentioned in parameter "Index", or it concatenates (or sums if it's not key values) key (or output) values from each index.

• If your Key range is a single cell with dimensions as well as Value range, those 2 cells must include the same loop (same loop name, with same size).

Example 1: Basic Example

Here are inputs (key and value ranges) for examples of groupby function:

There are dimensions in column Key1 and Value4. Here is an extract of grid flatten with all dimensions for column Key1 and Value4:

To aggregate values from Value3 according to Key2, you can write the following formula:

=MM_GROUPBY(MM_COLUMN(F3),MM_COLUMN(I3))

Or:

=MM_GROUPBY(F3:F10, I3:I10)

Note that in both formulas, size of row ranges are the same for keys or values.

Here is groupby grid:

By default, Value3 values are summed according to keys in Key2.

Example 2: with Key range with dimensions

Column Key1 from Table to group has dimensions taken from 2 columns:

At index 1, Key1 has values from first column of grid "Data for loop".

At index 2, Key1 has values from second column of grid "Data for loop".

If you want to groupby values from Value1 according to Keys in Key1, you should write this formula:

=MM_GROUPBY(E3:E10;G3:G10)

Here, no index was specified into groupby formula, so Keys from 2 columns will be concatenate, and values from Value1 will be aggregated according to each key from both columns:

Note that key "D" are in both indexes from Key1, values were summed according to key "D" present in both indexes.

Specified dimension

With the same example than before, you can also choose one specific dimension for your key range Key1:

=MM_GROUPBY(E3:E10;G3:G10;"SUM";"Data";1)

Here, "Data" is the loop name that include values from grid "Data for loop".

Thus, Values in Value1 will be summed according to keys in column 1 from "Data for loop":

Example 3: with Key and Value as single cell with dimensions

If your Key range is a single cell with dimensions as well as Value range, those 2 cells must include the same loop (same loop name, with same size).

Let's suppose (see upper picture) we have in cell C14 of your Excel model MM_LOOP formula with Key2 values, in cell A14 MM_LOOP formula with Value3 values, in cell B14 MM_LOOP formula with Values2 values (those loops have same name, same size, loop is named "Key" here).

Here is the formula to sum those values according to Key2:

=MM_GROUPBY(C14;A14:B14)

Here are results from the formula in Mind:

You can notice here that we can obtain same results by just entering as input parameters column ranges "Key2", "Value2" and "Value3 from grid "Table to group".