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.