Need Help with COUNTIF Referencing Specific Columns

[swolcott]

Our company did a strengths finder quiz and I am trying to consolidate the results. My current spreadsheet is formatted as below:

Name 1	Name 2	Name 3
Team of Name 1	Team of Name 2	Team of Name 3
Strength 1	Strength 1	Strength 1
Strength 2	Strength 2	Strength 2
Strength 3	Strength 3	Strength 3
Strength 4	Strength 4	Strength 4
Strength 5	Strength 5	Strength 5

Each person's results are in its own column, with about 30 columns of results (30 employees). While names are unique, there is overlap on the teams. I'd like to make a table that counts the frequency of each result for each team (i.e.: Operations) and matches it against a list of the possible strengths (that way the table can be sorted to show the most frequently occurring strengths on the team).

The current formula I am using is as follows:
=COUNTIF(INDEX('Company Results'!$B$5:$BA$9,0,MATCH("Operations",'Company Results'!$B$4:$BA$4,0)),[@Strength])

It seems to only count the strengths for the first person that meets the team criteria, though I am looking for a function that finds the sum of all employees within that team. Also, I can't seem to use a Pivot Table only because there are two subheaders per person (their name and team) before the results. Any ideas? Thanks in advance!

[sourabhg98]

Calculate individual totals first using sumif and then sum up according to the teams again using sumif.

Individual Count (array formula)-

Please Login or Register  to view this content.

Team Count-

Please Login or Register  to view this content.

excelforum003.xlsx

Regards
Sourabh

[swolcott]

Hi Sourabh,

Thank you for your quick response! I tried following your steps, but couldn't get it to work quite right (and it is likely my fault, but figured I'd check in again just in case).

It seems that the sum and sumif functions consolidated all "strengths" rather than counting each one. I am attaching the actual document I am working on for clarification.

On the "Programs Team Analysis" sheet, I have a table set up to count how many time each strength occurs across the team (refer to column B for the count; this is referencing the strengths that appear in column A). As you can see, it is currently only counting the strengths from Person 1 (located on the "Company Results" sheet). When I tried using sum/sumif, it ended up summing up all strengths into a single number rather than breaking them apart.

Thanks!

[sourabhg98]

Sorry I misinterpreted your question.

Use this formula in Cell B2-

Please Login or Register  to view this content.

Formula to be entered as array formula by pressing Ctrl+Shift+Enter instead of just enter.

Check attached.
Final StrengthsFinder2.xlsx

[swolcott]

Sorry I misinterpreted your question.

Use this formula in Cell B2-

Please Login or Register  to view this content.

Formula to be entered as array formula by pressing Ctrl+Shift+Enter instead of just enter.

Check attached.
Attachment 489949

Thank you so much - this worked perfectly!

[sourabhg98]

Glad to know that helped

If your query is resolved kindly mark the thread as solved from the "Thread Tools" at the top of this page.
You can also click on "add reputation" to add to the reputation of someone who has helped

Regards
Sourabh