Results 1 to 10 of 10
Thread: Count by Date Range

20010402, 18:39 #1
 Join Date
 Feb 2010
 Location
 Denver, CO
 Posts
 191
 Thanks
 0
 Thanked 0 Times in 0 Posts
Count by Date Range
I have a rather large Excel file (database) that I need to count by quarter. For example: xx number of people called during the 1st Qtr, xx in the second Qtr, etc. I tried using the COUNTIF function, but the criteria for this function doesn't accept a range of dates (or I can't get the syntax correct). I need, for instance, "count this row if 'call date' is between 10/01/2000 and 12/31/2000".
I am running Excel 97.
Randall Davis
Data Management Specialist
Wichita, KS

20010402, 20:01 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Count by Date Range
The following array formula will count the dates in the range A1:A50 that are in the fourth quarter:
<pre>=SUM(((A1:A50) >= DATEVALUE("10/1/2000")) * ((A1:A50) <= DATEVALUE("12/31/2000")))
</pre>
Since this is an array formula, you must hold down the Shift and Ctrl keys when you press Enter to enter it into a cell.Legare Coleman

20010402, 21:00 #3
 Join Date
 Feb 2010
 Location
 Denver, CO
 Posts
 191
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Count by Date Range
Great!! This works, but I don't understand how. I need to study up on Arrays!
Now, I need to go one step further. Is there a way to automagically calculate the rows that need to be counted. In the formula example you've given 'A1:A50' would change periodically. Is there a way to automate this?

20010402, 21:03 #4
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Count by Date Range
If you want a year independent date the following formula should work :
=SUM(IF(ROUNDUP(MONTH(DateRange)/3,0)=<font color=red>1</font color=red>,1,0))
where DateRange is the range of your dates to be evaluated (e.g. A1:A100) and the number in red is the quarter you want to count.
Again, like Legarre's formula, this is an array formula, so once you type it in you should hold CtrlShift whilst you press the Enter key. When you do that Excel will enclose it in {} brackets.
(The formula =ROUNDUP(MONTH(A1)/3,0) will return the Quarter of the date in A1)
Andrew C

20010402, 21:11 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Count by Date Range
As long at the range speified covers the maximum range, and the unused cells don't contain dates, then the formula should work. However, it might also be possible to create a range that would vary dynamically if you can tell me how to know how to vary it.
Legare Coleman

20010402, 22:46 #6
 Join Date
 Feb 2010
 Location
 Denver, CO
 Posts
 191
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Count by Date Range
Your formula works if I specify exactly the rows that contain data. If any of the rows 1:50 are blank (for instance, my data ran out on row 49), the answer defaults to 1.
My data gets added to weekly, and therefore the number of rows grows weekly through the course of our FY. How do I get the range to grow dynamically as the amount of data grows.
It is organize into a simple database list.

20010403, 05:04 #7
 Join Date
 Apr 2001
 Location
 Levin, ManawatuWanganui, New Zealand
 Posts
 324
 Thanks
 9
 Thanked 28 Times in 26 Posts
Re: Count by Date Range
Hi
Try this simple function
Function CalculateQtr(callDate As Date)
Dim intQtr, intMonth As Integer
intMonth = Month(callDate)
Select Case intMonth
Case 1 To 3
intQtr = 1
Case 4 To 6
intQtr = 2
Case 7 To 9
intQtr = 3
Case Else
intQtr = 4
End Select
CalculateQtr = intQtr
End Function
If you put this in the personal workbook and then use the f(x) tool to apply it you will see a formula like this
=CalculateQtr(b2).
This assumes that b2 contains a call date.
Then sort data by CalculateQtr rsults, use the DATASuBtotal facility to and count at each change in Qtr et voila.
Good luck
Cheers
Geof

20010403, 10:53 #8
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Count by Date Range
If you are referring to the formula I posted above, I am sorry that I did not reply sooner, but thought you were referring to Legarre's formula.
I should have mentioned that formula would treat blank cells as 1/1/1900 (Month(0) = 1 in Excel), and hence evaluate to Qtr 1. The following is an amended version which should work for blanks, but not for values that are not dates, even spaces.
<pre> =SUM(IF(IF(A1:A50<>0,ROUNDUP(MONTH(A1:A50)/3,0))=1,1,0))</pre>
sorry again.
Andrew C

20010403, 16:42 #9
 Join Date
 Feb 2010
 Location
 Denver, CO
 Posts
 191
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Count by Date Range
Oops. As I would make changes to the formula, I forgot to make it an array. Your formula does work as designed. Thanks for everyone's help. My first post to the Lounge was a rousing success!!

20010404, 06:50 #10
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Count by Date Range
I tested my formula in both Excel 97 and Excel 2000, and it worked fine if I specified the complete range but did not have dates in all cells in the range. Are you sure that you held Shift+Ctl when you entered the foumula? What you describe is what would happen if you did not, and the first cell in the range was in the quarter being tested for. In any event, the following formula should test only the cells that contain something in the range A1:A50, as long as there are no cells containing data after the first empty cell in the range.
<pre>=SUM(((A1:OFFSET(A1,MAX(1,COUNTA(A1:A50))1,0,1,1)>=DATEVALUE("1/10/2000")))*((A1:OFFSET(A1,MAX(1,COUNTA(A1:A50))1,0,1,1))<=DATEVALUE("31/12/2000")))
</pre>
Sorry if you have to scroll to the right to see all of that.
*** Geoff W. I removed the "pre" tags with long lines, they cause problems with viewing ***
*** Legare Coleman: I put the pre tags back in. Not having them causes problems with symbols in those long lines getting changed by the board software ***Legare Coleman