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.

Thanks
James

 

[Aladin Akyurek]

What is the relation of SCORE and/or TEAM with Task 1 to 5?

 

[Start123]

Score & Tasks

The scores are simply what an individual has scored in task 1, task 2, task 3 etc
The full sheet actually shows, in column A, a list of people's names, in column B, what team they belong to, and in columns C-G, the scores that each person received in each particular task. C1:G1 contains the task names (task 1, task 2, task 3 etc)

I tried to simplify it before, but maybe this is more useful?

Hope you can help

James

 

[Lewiy]

Can you not use SUMIF? i.e. assuming your dropdown list is in say L2:

=SUMIF(A2:A12,L2,B2:B12)
[code]

 

[Aladin Akyurek]

Re: Score & Tasks

The scores are simply what an individual has scored in task 1, task 2, task 3 etc
The full sheet actually shows, in column A, a list of people's names, in column B, what team they belong to, and in columns C-G, the scores that each person received in each particular task. C1:G1 contains the task names (task 1, task 2, task 3 etc)

I tried to simplify it before, but maybe this is more useful?

Hope you can help

James

=SUMIF($B$2:$B$30,Team,INDEX($C$2:$G$30,0,MATCH(Task,$C$1:$G$1,0)))

 

[Start123]

Thank you - but just need a little bit more...

Thank you very much for your help so far.

The INDEX/MATCH combo is great and works a treat for finding what I'm looking for.

However, I actually need to display the AVERAGE score for a given task, according to the team listed in the drop down list.

So lets say the drop down list says 'Global' (i.e. for the Global team). I need Excel to look for Global as a criteria of the index/match for the task, then display the average score for that team for that task in question (say, task 1, for instance)

Do you see what I mean? Hope it makes sense

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF($B$2:$B$30=Team,INDEX($C$2:$G$30,0,MATCH(TAsk,$C$1:$G$1,0))))

If C2:G30 can contain empty cells, try the following instead...

=AVERAGE(IF($B$2:$B$30=Team,IF(INDEX($C$2:$G$30,0,MATCH(TAsk,$C$1:$G$1,0))<>"",INDEX($C$2:$G$30,0,MATCH(TAsk,$C$1:$G$1,0)))))

Hope this helps!