Average if using multiple criteria

[Jeff]

Hello all. I could use a little help. I've tried working
with average and an array formula and can work with one
criteria but I am having trouble adding a second.

My spreadsheet is set up as follows

column a: column b: column c:
customer a 1/15/04 25
customer b 1/26/04 41
customer a 1/30/04 32
customer c 2/01/04 51
customer a 2/10/04 14

How do I find the average of column c where column a
equals "customer a" and column b is between 1/01/04 and
1/31/04?

According to the above example it would average 25 and 32
but not the 14 since it is past 1/31/04.

Thank you in advance.

 

[JulieD]

Hi Jeff

there's probably other ways but here's two:

=SUMPRODUCT((A2:A6=C8)*(B2:B6>=C9)*(B2:B6<=C10)*C2:C6)/SUMPRODUCT((A2:A6=C8)
*(B2:B6>=C9)*(B2:B6<=C10))

and

=SUMPRODUCT((A2:A6=C8)*(TEXT(B2:B6,"mmm")="Jan")*C2:C6)/SUMPRODUCT((A2:A6=C8
)*(TEXT(B2:B6,"mmm")="Jan"))

where C8 contains "customer a"
and C9 contains the start date of your range
and C10 contains the end date of yor range

Cheers
JulieD

 

[JulieD]

Hi Frank

still can't get my head around array formulas , whereas the sumproduct
makes perfect sense to me <vbg>

cheers
JulieD

 

[Frank Kabel]

Hi Julie
as alternative using the array formula:
=AVERAGE(IF(A2:A6=C8)*(B2:B6>=C9)*(B2:B6<=C10),C2:C6))

 

[Frank Kabel]

Hi Julie
as long as it works who cares
Maybe the SUMPRODUCT variant is even faster...