Dynamic Two-Way Aggregation

An Excel file that teaches how to perform Dynamic Two-Way Aggregation operation.

Excel-Based Dynamic Two-Way Aggregation

First, the detailed sales list consisting of City, Size and Qty columns is arranged in table format on the worksheet. Unique cities in the City column are automatically listed as row headers in the summary table on the right with the UNIQUE and SORT functions. In the column headings, L, M and S sizes are written manually and constitute the second dimension of the dynamic range. The SUMIFS function uses the Qty column in the data table along with the City and Size columns as criteria; Amounts are automatically collected for each city and size combination by referring to the row and column ranges entered with the expressions F5# and G4# in the formula. Thus, when new records are added or the city list changes, the two-way summary table is automatically updated.

Hope it is useful.