Count Occurrences Date Range Falls Within Month

**Rbooth** · 02-24-2014, 03:24 AM

MLS Pending Sales.xlsx

Hello,

I have spent hours trying to figure this out on my own and have poured through online threads to find the answer, but have not succeeded. I am working on a Real Estate Market Report and am trying to track the homes that were "Pending" during previous months. Unfortunately, my local MLS does not track this data directly. There is no way to look back at January 2006 to see how many homes were "Pending" in that month. However, they do provide the date the home went into pending status as well as a closing date: providing the entire period the home was Pending. I have attached a workbook with three sheets; two for each year, and one for the report. In the Year sheets Column A holds the Pending Date and in B Closing Date. On the report sheet I have listed each month from A3:A14, and "2006" in B2 and "2007" in C2. I want to count how many homes were in a pending status for each month. I have tried using countifs as well as sumproduct, but can't seem to find the answer. Any help would be greatly appreciated.

**ajryan88** · 02-24-2014, 05:16 AM

Hi,

I think this gives you what you want. I have used the combination of 2 SUMPRODUCT formulae, so you were probably very close when you attempted this using your own SUMPRODUCT formula.

Hope this helps

**Pete_UK** · 02-24-2014, 05:20 AM

Insert a new column A in your report sheet, and fill the month numbers 1 to 12 in cells A3:A14. Then you can use this formula in C3:

=SUMPRODUCT(('2006'!$A$2:$A$100<=DATE(C$2,$A3+1,0))*('2006'!$B$2:$B$100>=DATE(C$2,$A3,1)))

and copy down. You can use a very similar formula in D3, i.e.:

=SUMPRODUCT(('2007'!$A$2:$A$100<=DATE(C$2,$A3+1,0))*('2007'!$B$2:$B$100>=DATE(C$2,$A3,1)))

where only the sheet name has changed (shown in red), although it will just show zeros with your sample data as the dates are the same as in sheet 2006.

Hope this helps.

Pete