A new GroupBy function is now available for Excel 365 users only.  This new function is very powerful and convenient to create quick group totaling without having to create Pivot Tables. Though pivot tables are indeed powerful and have their own advantages, the GroupBy function is an excellent alternative to do some simple and quick group totaling.  The huge benefit is that you only have to write one formula (the first cell) and the function will automatically fill out all the group names (left column), the subtotals (right column) and the grand total (bottom row).

Scenario:

In our example (Figure 1), there are multiple department transactions listed with various quantities and order totals.  The objective is to provide each department total (aggregate sums) and a grand total of all the departments.  Use the new GroupBy function (cell G3 in the screenshot) to create all the department subtotals and grand total mentioned in the objective.

 

Objective: Use the GroupBy function (Office 365 only) to create aggregate group sums for all the department order totals.

 

Steps:

  1. Convert the data range into a table (this step is not required, but it is useful when adding new data rows to the transaction table)
  2. Click in cell G3 (or any cell where you want the report to be created
  3. Type the GroupBy, and press the ENTER key

    Syntax:   =GROUPBY(B3:B21,D3:D21,SUM)

    ¨  B3:B21 is the range with all the department names (not including the header)
    ¨  D3:D21 is the range with all the Order Totals for each department transaction
    ¨  SUM is the action performed on the Order Totals

Note:  No spaces in the formula but you may type in upper or lower case.