Average by Multiple Criteria.

[RoadKill]

How can I try to do an average requiring multiple criteria? For example, I
want to do it by Employee Name and Date Range.

So if cells A1:A100 is the Name, B1:B100 is the Date and C1:C100 is the
score to be averaged, how can I have it pull an average for John Doe for
April 1-15th?

Thank you

 

[Chin via OfficeKB.com]

If you are using Excel2007, you may try:

=AVERAGEIFS(C1:C100,A1:A100,"John Doe",B1:B100, ">3/31/09",B1:B100,"<4/16/09")


Please advise if this helps.

 

[Sheeloo]

=SUMPRODUCT(--(A1:A100 ="John Doe"),--(B1:B100 >= DATE(2009,4,1)),--(B1:B100
<= DATE(2009,4,15)),(C1:C100 ))
will give you the sum
and
=SUMPRODUCT(--(A1:A100 ="John Doe"),--(B1:B100 >= DATE(2009,4,1)),--(B1:B100
<= DATE(2009,4,15)),--(C1:C100<>"" ))
will give you the count

So your average will be
=SUMPRODUCT(--(A1:A100 ="John Doe"),--(B1:B100 >= DATE(2009,4,1)),--(B1:B100
<= DATE(2009,4,15)),--(C1:C100<>"" ))/SUMPRODUCT(--(A1:A100 ="John
Doe"),--(B1:B100 >= DATE(2009,4,1)),--(B1:B100 <=
DATE(2009,4,15)),--(C1:C100<>"" ))

 

[T. Valko]

Try this array formula**.

Use cells to hold the criteria.

E1 = John Doe
F1 = 4/1/2009
G1 = 4/15/2009

=AVERAGE(IF((A1:A100=E1)*(B1:B100>=F1)*(B1:B100<=G1),C1:C100))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.