Google sheets - summarize matrix values-CodePudding

I have data like below:

1 2 3  A B C
4 5 6  A B C
7      C
3 4    B C
8 9    C B
1 2 3  C A B

The values 1-9 are assigned to values A, B, C - so for instance in first row A=1, B=2, C=3 etc.

I need to summarize all the elements above to get A, B, C.

How to start with this problem?

Final results:

A 7
B 22
C 29

Edit: https://docs.google.com/spreadsheets/d/1kzOtV9_SE5s7DaA_6YcZq6z08FpfXaIAjukyISew49U/edit?usp=sharing

CodePudding user response：

This should do your summary provided the ranges are the same dimensions.

=QUERY({FLATTEN(Arkusz1!F2:H)\FLATTEN(Arkusz1!B2:D)};"select Col1,SUM(Col2) where Col1<>'' group by Col1 order by SUM(Col2) desc label Col1'Summary'")

It is installed in a new tab called MK_Help