Summarize a skills rating table with an unknown number of skill columns in Google Sheet using pure formulas and no GAS

Question

I am unable to share a working sheet.

I have a sheet with a table like so:

Name	Skill 1	Skill 2	Skill 3	...
one	high	medium	low	none
two
three	low	medium	high	none
four	low	high	hig
...

• It has an unknown number of rows
• It has 3 skill columns today, but more skills may be added later
• Not all rows are filled out

I want to summarize the table like so:

Skill	high	medium	low	none
Skill 1	1	0	2	0
Skill 2	1	2	0	0
Skill 3	1	1	1	0
...

Basically I am showing each skill and how many high/medium/low/none they have.

I am trying to use formulas so everything is dynamic. Meaning, if more names are added, or if more skills are added, then the table automatically shows it.

I can get a list of skills from the first table like so:

={
    "Area";
    TRANSPOSE(SORT(Ratings!B1:1))
}

But that is as far as I got.

Answer 1

use:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(Ratings!B2:5000="",,Ratings!B1:1&"×"&Ratings!B2:5000)), "×"), 
 "select Col1,count(Col1) where Col2 is not null group by Col1 pivot Col2 label Col1'Skill'"))