formulas
 

Please help...

my "home" data:
SqFt YR OLP LP SP COE
3,067 2000 899,000 750,000 760,000 7/11/2007
3,067 1999 1,499,000 1,499,000 1,300,000 4/24/2007
2,975 1999 1,150,000 1,150,000 1,150,000 4/15/2008
2,413 1991 845,000 799,000 760,000 3/4/2003

Need total count for homes that closed in 2007
then need to have formula that averages the sp for the homes that closed in
that year.

Thanks

formulas
 

=SUMPRODUCT((YEAR($F$2:$F$5)=2007)*1)

Formula below is an ARRAY formula that must be entered using
ctrl+shift+enter
=AVERAGE(IF(YEAR(F2:F11)=2007,E2:E11))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stacey" wrote in message
...
Please help...

my "home" data:
SqFt YR OLP LP SP COE
3,067 2000 899,000 750,000 760,000 7/11/2007
3,067 1999 1,499,000 1,499,000 1,300,000 4/24/2007
2,975 1999 1,150,000 1,150,000 1,150,000 4/15/2008
2,413 1991 845,000 799,000 760,000 3/4/2003

Need total count for homes that closed in 2007
then need to have formula that averages the sp for the homes that closed
in
that year.

Thanks

formulas
 

Hi,

In 2007 you can use these two formulas:
=COUNTIFS(F2:F5,"=39448",F2:F5,"<=39813")
and
=SUMIFS(E2:E5,F2:F5,"=39448",F2:F5,"<=39813")

F2:F5 is the closing date range and E2:E5 is the SP
39448 is the numerical equivalent of 1/1/2008 and
39813 is the numerical equivalent of 12/31/2008

You could use the slightly longer but clearer:

=COUNTIFS(F2:F5,"=1/1/08",F2:F5,"<=12/31/08")
=SUMIFS(E2:E5,F2:F5,"=1/1/08",F2:F5,"<=12/31/08")


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Stacey" wrote:

Please help...

my "home" data:
SqFt YR OLP LP SP COE
3,067 2000 899,000 750,000 760,000 7/11/2007
3,067 1999 1,499,000 1,499,000 1,300,000 4/24/2007
2,975 1999 1,150,000 1,150,000 1,150,000 4/15/2008
2,413 1991 845,000 799,000 760,000 3/4/2003

Need total count for homes that closed in 2007
then need to have formula that averages the sp for the homes that closed in
that year.

Thanks