Sum of Vlookup

[Start123]

Hi

I have data arranged in a matrix as follows:

Cells A2:A12

TEAM
Global
Local
Documentation
Local
Local
Global
Documentation
Global
Global
Global

Cells B2:B12

SCORE
1
4
2
3
3
3
4
5
2
2

Cells B1:F1

Task 1
Task 2
Task 3
Task 4
Task 5

I have a drop down list with the teams listed (Global, Local, Documentation)

The idea is that users select a team and get to see that team's scores for the tasks in question. So I need a formula that will search for the team name displayed in the drop down list, add up the total scores applicable to that team and then return the average so as I have an overall team score (the idea is that the team names listed in cells A2:A12 are associated with a list of individuals, listed in another column, hence why some are repeated)

The formula will need to be some form of VLOOKUP or INDEX/MATCH combo as, for each cell its entered into, it needs to return the correct value relevant to the tasks shown in cells B1:F1

I hope the above makes sense and that someone can help.

 

[barry houdini]

So, I assume you also have score in C2:F12 associated with tasks 2 to 5?

If your dropdown is in A14 and you want to show an average for each task in cells B14:F14, for the team shown in A14 then try this formula in B14 copied across

=SUMIF($A2:$A12,$A14,B2:B12)/COUNTIF($A2:$A12,$A14)

or

=AVERAGE(IF($A2:$A12=$A14,B2:B12))

which needs to be confirmed with CTRL+SHIFT+ENTER