Count by Date Range

Topic

New Reply

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

Reply | Quote

Replies

The following array formula will count the dates in the range A1:A50 that are in the fourth quarter:

=SUM(((A1:A50) >= DATEVALUE("10/1/2000")) * ((A1:A50) <= DATEVALUE("12/31/2000")))

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

	=SUM(IF(IF(A1:A500,ROUNDUP(MONTH(A1:A50)/3,0))=1,1,0))

sorry again.

Andrew C

Reply | Quote

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.

=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")))

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 ***

Reply | Quote

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!!

Reply | Quote

If you want a year independent date the following formula should work :-

=SUM(IF(ROUNDUP(MONTH(DateRange)/3,0)=1,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 Ctrl-Shift 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

Reply | Quote

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 DATA|SuBtotal facility to and count at each change in Qtr et voila.
Good luck
Cheers
Geof

Reply | Quote