# averaging a score for a team of people

#### silentcoates

##### New Member
Hello

In column A I have a list of dates, column B a list of names in no particular order and in column c i have a list of scores.

on sheet 2 ( called Lynnes team) I have a list of 6 people all of which appear in column B in sheet 1 (called input sheet) I need a total cell for that team that averages there scores. Column C will also have balnk cells and I need the formula to ignore blank cells and only average actual scores.

oh and one last thing it has to be dependent on dates. in sheet 1 I have two dates (a from and too date) in cells F2 and G3. I need the formula to look at column a and only average scores between the two dates in F2 and G2.

hope that all makes sense.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### FrankFahrendorf

##### New Member
Hi SilentCoates

I think you want to do something like this:

A B C D E F G
1 date name score 04-03-2005 06-03-2005
2 01-03-2005 John 3 John 7
3 02-03-2005 Alice 2 Alice 9
4 03-03-2005 Mike 5 Mike 6
5 04-03-2005 John 6
6 05-03-2005 Kim 3
7 06-03-2005 Sean 2
8 07-03-2005 Maria 6
9 01-03-2005 Dennis 7
10 02-03-2005 John 3
11 03-03-2005 Mike 2
12 04-03-2005 Alice 1
13 05-03-2005 Maria 5
14 06-03-2005 John 0
15 07-03-2005 Kim 8
16 01-03-2005 Sean 9
17 02-03-2005 Mike 3
18 03-03-2005 Alice 4
19 04-03-2005 Mike 6
20 05-03-2005 Alice 8
21 06-03-2005 Maria 5
22 07-03-2005 Maria 3
23 01-03-2005 Dennis 2
24 02-03-2005 John 6
25 03-03-2005 Sean 8
26 04-03-2005 Maria 9
27 05-03-2005 Dennis 0
28 06-03-2005 John 1
29 07-03-2005 Alice 0

How did I get John 7, Alice 9 and Mike 6:

I used array formulas. Do this:

next to John put this formula =SUM((\$A\$2:\$A\$29>=\$F\$1)*(\$A\$2:\$A\$29<=\$G\$1)*(\$E\$2=\$B\$2:\$B\$29)*\$C\$2:\$C\$29)

but instead of just hitting enter, do CTRL SHFT ENTER

Best regards

Frank

#### silentcoates

##### New Member
Hello,

this does not work, please imagine a team made up of the follwoing names from your list
John
Alice
Sean
I need a formula that looks at all there scores within a certain date period and then averages their scores to provide an average for that team. But it also has to ignore an 0 or blank scores

#### FrankFahrendorf

##### New Member
OK I think I see what you want.

You still need the sum formula I gave, but you also need to count the number of scores different from 0 or blank. You can use the formula as for sum:

=SUM((\$B\$2:\$B\$29>=\$E\$1)*(\$B\$2:\$B\$29<=\$F\$1)*(\$E2=\$C\$2:\$C\$29)*(D2:D29>0))

That should give you something like this:

As per example above you have

John 7 2
Alice 9 2
Sean 2 1

Now you can make a weighted average (Assume John is in cell E2):

=SUMPRODUCT(F2:F4/G2:G4,G2:G4)/SUM(G2:G4)

that gives 3.6.

You cannot do all of the above in one cell unless you write a macro.

#### Peter_SSs

##### MrExcel MVP, Moderator
silentcoates

If I have understood correctly, and you are just looking for the average for the team between two dates, then see if this is what you want.

The dates in question are in F2 and G2 on 'Input Sheet'. I have repeated the dates in F2 and G2 on 'Lynnes Team' but these repeated dates are for information only and are not used in the formula.

On 'Input Sheet' I have manually highlighted the column C cells that are between the dates and belong to members of Lynnes team. Again this was just to help in my checking. In my example then, I am trying to get the formula to average 5, 8 and 9 (ignoring the blank).

So here is the Input Sheet:
Mr Excel.xls
ABCDEFG
1DateNameScoreFromTo
201/11/2005Alice104/11/200510/11/2005
302/11/2005Bill2
403/11/2005Alice3
504/11/2005Don4
605/11/2005Sean5
706/11/2005Alice
807/11/2005Tom7
908/11/2005Sean8
1009/11/2005Alice9
1110/11/2005John10
1211/11/2005Ann11
1312/11/2005Bill12
Input Sheet

And here is 'Lynnes Team' sheet. The formula in F6 is:
=AVERAGE(IF(('Input Sheet'!\$A\$2:\$A\$13>'Input Sheet'!\$F\$2)*('Input Sheet'!\$A\$2:\$A\$13<'Input Sheet'!\$G\$2)*('Input Sheet'!C2:C13<>""),IF(ISNUMBER(MATCH('Input Sheet'!\$B\$2:\$B\$13,\$A\$2:\$A\$4,0)),'Input Sheet'!\$C\$2:\$C\$13,"")))
which must be confirmed with CTRL+SHIFT+ENTER
Mr Excel.xls
ABCDEFG
1Lynnes TeamFromTo
2John04/11/200510/11/2005
3Alice
4SeanAverage for this team
5between above dates
67.33333333
7
Lynnes Team

Replies
2
Views
82
Replies
3
Views
331
Replies
1
Views
122
Replies
0
Views
240
Replies
2
Views
89

1,195,596
Messages
6,010,636
Members
441,558
Latest member
lambierules

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