Average by Multiple Criteria.

  • Thread starter Thread starter RoadKill
  • Start date Start date
R

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
 
=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<>"" ))
 
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.
 
Back
Top