Sum of Vlookup

Start123

Board Regular
Joined
May 14, 2007
Messages
80
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
Can you not use SUMIF? i.e. assuming your dropdown list is in say L2:
Code:
=SUMIF(A2:A12,L2,B2:B12)
[code]
 
Upvote 0
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)))
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,430
Messages
6,172,055
Members
452,444
Latest member
ShaImran193

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top