Summarize Data in Every Two Columns under Each Category

Problem description & analysis:

In the Excel table below, column A contains categories and there are 2N key-value formatted columns after it:

We need to group rows by the category and the key and perform sum on detail data. The expected result set will have 3 columns. Note that the result set should be arranged according to the original order of the category column.

Solution:

Use SPL XLL to enter the following formula and drag it down::

=spl("=E(?).groupc@r(Country;;Label,Count).groups@u(Country,Label;sum(Count):Total)",A1:G11)

As shown in the picture below:

Explanation:

E()function reads data in its original table format. groupc@r performs column-to-row transposition by putting every n column in one group. groups() function performs grouping & aggregation.

Comments

[Judith-Excel-Sharing]

For more references:
🔹SPL download address: scudata.com/download-Desktop
🔹Plugin Installation Method: c.scudata.com/article/1652061135502
🔹References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524
🔹SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...