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.
CodePudding user response：
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'"))
