# 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.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### barry houdini

##### MrExcel MVP
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

Replies
1
Views
234
Replies
0
Views
199
Replies
1
Views
582
Replies
0
Views
267
Replies
0
Views
141

1,186,164
Messages
5,956,302
Members
438,246
Latest member
Deelea

### 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.

### Which adblocker are you using?

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