Sum of Vlookup

Start123

Board Regular
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

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

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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

Start123

Board Regular

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

Well-known Member
Can you not use SUMIF? i.e. assuming your dropdown list is in say L2:
Code:
``````=SUMIF(A2:A12,L2,B2:B12)
[code]``````

MrExcel MVP

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

Start123

Board Regular
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

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

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

Hope this helps!

Replies
1
Views
551
Replies
2
Views
1K
Replies
11
Views
940
Replies
15
Views
598
Replies
0
Views
356

1,181,658
Messages
5,931,272
Members
436,786
Latest member
Deniel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back