Average if using multiple criteria

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.

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")*C 2: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

"Jeff" wrote in message
...
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.

Hi Frank

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

cheers
JulieD

"Frank Kabel" wrote in message
...
Hi Julie
as alternative using the array formula:
=AVERAGE(IF(A2:A6=C8)*(B2:B6=C9)*(B2:B6=C10),C2: C6))

--
Regards
Frank Kabel
Frankfurt, Germany


JulieD wrote:
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:A
6=C8)
*(B2:B6=C9)*(B2:B6=C10))

and


=SUMPRODUCT((A2:A6=C8)*(TEXT(B2:B6,"mmm")="Jan")*C 2: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

"Jeff" wrote in message
...
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.

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

--
Regards
Frank Kabel
Frankfurt, Germany


JulieD wrote:
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:A
6=C8)
*(B2:B6=C9)*(B2:B6=C10))

and


=SUMPRODUCT((A2:A6=C8)*(TEXT(B2:B6,"mmm")="Jan")*C 2: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

"Jeff" wrote in message
...
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.

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

--
Regards
Frank Kabel
Frankfurt, Germany


JulieD wrote:
Hi Frank

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

cheers
JulieD

"Frank Kabel" wrote in message
...
Hi Julie
as alternative using the array formula:
=AVERAGE(IF(A2:A6=C8)*(B2:B6=C9)*(B2:B6=C10),C2: C6))

--
Regards
Frank Kabel
Frankfurt, Germany


JulieD wrote:
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:A
6=C8)
*(B2:B6=C9)*(B2:B6=C10))

and



=SUMPRODUCT((A2:A6=C8)*(TEXT(B2:B6,"mmm")="Jan")*C 2: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

"Jeff" wrote in message
...
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.