Return Results Based on Date Range

**ExcelSuduko** · 04-05-2013, 08:52 AM

I have a 2 sheet workboook. Sheet tab 1 (External Comm) contains all of the records, currently about 500 and growing. Sheet 2 (CalcSheet) is a compilation of the results in numbers (sum), based on selected results. Compiling all of the results is easy for the whole list:

=COUNTIF('External Comm'!E7:E1000,"Tour") There are currently 134 records defined as "Tour"

Now I am trying to select the same type of results, but based on a date range (column A of sheet tab 1). Here is my current formula but it returns a negative number of -314. The correct number should be 23 Tours for the dates I indicated.

=SUMPRODUCT(-('External Comm'!A7:A1000>=DATEVALUE("4/1/2010")*('External Comm'!A7:A1000<=DATEVALUE("3/31/2011")*('External Comm'!F7:F1000="Tour"))))

One other item, the current range of records are from row 7 to row 407. However, I used a Macro to insert a new row in row 7, moving all current records down a row. I use the range of "1000" to allow for future growth, so I am wondering if the blank rows from 408 to 1000 is causing the problem???

Thanks for any assistance and help.

**Cheeky Charlie** · 04-05-2013, 09:28 AM

Hard to say without an example, but some ideas:
Use full column ranges (no, blanks should not be an issue, although this would depend on how you're done it)
Use sumifs or countifs if you're in Excel 2010, the sumproduct formula is inefficient and harder to edit / understand. Incidentally, you've done it wrong (you've put a - on the front, which is why you're getting a negative answer); but rather than correct it, use sumifs/countifs.
Hardcoding your dates into your formula is not great practice, I would recommend putting your boundary dates in cells then referring to them in your formula.

**etaf** · 04-05-2013, 09:42 AM

you could try using countIFs and add the date criteria

=COUNTIFS('External Comm'!A7:A1000,">=4/1/2010",'External Comm'!A7:A1000,"3/31/2011", 'External Comm'!F7:F1000, "Tour")

**ExcelSuduko** · 04-05-2013, 10:10 AM

Great, thanks Etaf. I did make one minor modification, adding <= before the last date. Cheeky Charlie, thanks also, I like idea of the date cells and will set the sheet up like that.

**etaf** · 04-05-2013, 10:26 AM

adding <= before the last date

oppps missed that off - sorry, thanks for the correction
useful if some one is searching the site

so modified and reposted

=COUNTIFS('External Comm'!A7:A1000,">=4/1/2010",'External Comm'!A7:A1000,"<=3/31/2011", 'External Comm'!F7:F1000, "Tour")

**Cheeky Charlie** · 04-05-2013, 10:45 AM

Whole column references, (hardly) slower, but surprisingly easier to read...

=COUNTIFS('External Comm'!A:A,">=4/1/2010",'External Comm'!A:A,"<=3/31/2011", 'External Comm'!F:F, "Tour")

**ExcelSuduko** · 04-05-2013, 11:01 AM

Trying to get my head around referencing two date range cells on sheet 2, however the returned results are (0). Do I need to call out the sheet name (CalcSheet) as well as the cell, or just the cell?

=COUNTIFS('External Comm'!A7:A1000,">='CalcSheet'!E20",'External Comm'!A7:A1000,"<='CalcSheet'!F20", 'External Comm'!F7:F1000, "EMS Information")