formulas

[Stacey]

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

 

[Don Guillett]

=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))

 

[Shane Devenshire]

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")