Formulas help

[Stacey]

I need to do summary tables for worksheets and am having trouble.

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/2006
2,413 1991 845,000 799,000 760,000 3/4/2008


I need to count how many "homes" close in a year, i.e., count number for
2007, then need to avg sp for all homes that had a coe in 2007.

How do I write a statement that looks for a coe with the same year then
averages the sp for closings in that year. Thanks. I am ready to pull my
hair out.

 

[Fred Smith]

To count the number of homes sold in 2007, use:
=SUMPRODUCT(--(YEAR(F2:F1000)=2007))
Adjust your range to suit.
Better yet, put 2007 into a cell (like H1), so you can change it at will,
and use:
=SUMPRODUCT(--(YEAR(F2:F1000)=H1))

Assuming this is in H2, to get the average SP:
=SUMPRODUCT(--(YEAR(F2:F1000)=2007),E2:E1000)/H2

If you want to make life easier for you, and impress your friends and
co-workers, look into Pivot Tables. They will do everything you want, and
more.

Regards,
Fred.