Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |