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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,547
Members
425,481
Latest member
ihumanl

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
Top