Counting value recurrence by index

[marmanlive]

Hello

In my spreadsheet, I have the results of research.

In the column, B3:B75 the skills are listed.
In range C3:G75 it marks whether a particular company is seeking that skill (mostly through the index "Yes").

Now I need to know which skills are the most required (appearing the most) across all companies, which ones are less important and so on...

For example:

• Vision - 4
• Market Research & Analysis - 2, etc.

COUNTIF is not an option, as the occurrence does not consider the value itself but an index for it.

Any help or hint greatly appreciated.

/Marat

[HansDouwe]

Why is the result of vision 14?
I only see sheet research checklist whit 1 skill Vision and 4 Yeses?

[marmanlive]

It's from the original file before I cut out the info for attaching the spreadsheet.
Just changed the info in the first post, thank you @HansDouwe
Sorry for confusion

[AliGW]

Mock up a few rows of sample results data manually - put it where you want to se it in the format you want.

[marmanlive]

Mock up a few rows of sample results data manually - put it where you want to se it in the format you want.

Hi AliGW, not sure what you mean by that, could you please elaborate on that? Thanks

[HansDouwe]

An option:

Create a helper column H: =COUNTA(C3:G3) and copy down and try

=CHOOSECOLS(SORT(B3:H75,7,-1),1,7)

[marmanlive]

Thank you @HansDouwe
that actually solved my first problem, which is to determine the occurrence rate of each skill set.
Yet I just figured out that I can't use CHOOSECOLS-fields for the pivot tables to capture data for charts...

Best regards

[Gregb11]

So you probably need to give us the full scenario so we can test what works and what doesn't. I have a different formula that doesn't use CHOOSECOLS, but you probably can't use it either Using the same helper column @HansDouwe uses, the formula would be:

=SORT(CHOOSE({1,2},B3:B75,H3:H75),2,-1)