Adding Cells in Date Range

[MagiCat]

I'm trying to come up with a formula which will add the cells in a date range. This date range may be within the same month or span several months but will always be within the year.

On the results page, Cell A3 will be the Employee Number, Cell B3 will be the From Date, Cell C3 will be the To Date. I need to find a for cell D3, the sum. For example, if A3 had 123456, B3 had 1/2/09, C3 had 1/3/09, then D3 would need to have 17.48 (8.88+8.6)

The way I'm currently looking at is adding a row between C and D on the data table below which contains the row number, then doing a VLOOKUP and a few indexes to pull the data but I'm concerned that pulling data from 7 different tables (for different types of employee hours) for 250 employees would make this take forever to recalculate.

Note that the table below has 12 rows for the 12 months of the years and the columns go all the way to 31. It can be counted on that if a date doesn't exist (for example, 2/30/09, the value in the cell will be 0 which means we can use ranges through the end of the month if needed)

Hours 20090131.xls
	A	B	C	D	E	F	G	H
1	EmployeeNumber	EmployeeName	Month\Day	1	2	3	4	5
2	123456	WASHINGTON,GEORGE	1	0	8.88	8.6	0	8.98
3			2	0	8.53	8.65	8.65	8.97
4			3	0	0	0	0	0
5			4	0	0	0	0	0
6			5	0	0	0	0	0
7			6	0	0	0	0	0
8			7	0	0	0	0	0
9			8	0	0	0	0	0
10			9	0	0	0	0	0
11			10	0	0	0	0	0
12			11	0	0	0	0	0
13			12	0	0	0	0	0
14	251833	LINCOLN,ABE	1	0	9.98	7.52	0	9.13
15			2	0	7.93	8.52	6.53	8.58
16			3	0	0	0	0	0
17			4	0	0	0	0	0
18			5	0	0	0	0	0
19			6	0	0	0	0	0
20			7	0	0	0	0	0
21			8	0	0	0	0	0
22			9	0	0	0	0	0
23			10	0	0	0	0	0
24			11	0	0	0	0	0
25			12	0	0	0	0	0
RegHoursImport

 

[gardnertoo]

Is there a reason you can't put the data in column format? If you can, here's a quick formula:

Book1
	A	B	C	D
1	from:	1/6/2009
2	to:	1/18/2009
3
4	hours	59	43
5	emp num	23	45
6	emp name	abe linc	geo wash
7	1/1/2009	1	6
8	1/2/2009	2	6
9	1/3/2009	3	1
10	1/4/2009	7	1
11	1/5/2009	7	3
12	1/6/2009	0	1
13	1/7/2009	5	3
14	1/8/2009	6	7
15	1/9/2009	6	6
16	1/10/2009	4	1
17	1/11/2009	3	2
18	1/12/2009	3	1
19	1/13/2009	5	5
20	1/14/2009	7	7
21	1/15/2009	5	0
22	1/16/2009	4	0
23	1/17/2009	6	7
24	1/18/2009	5	3
25	1/19/2009	1	3
Sheet1

 

[gardnertoo]

Here's a formula for cell D3 that works with the data in the format you have:

=SUM(OFFSET(Sh1!$C$2,MATCH(A3,Sh1!A:A,0)+MONTH(B3)-1-MATCH("Month\Day",Sh1!C:C,0),DAY(B3),1,(MIN(C3-B3+1,31))))+(MONTH(C3)>MONTH(B3))*SUM(OFFSET(Sh1!$C$2,MATCH(A3,Sh1!A:A,0)+MONTH(C3)-1-MATCH("Month\Day",Sh1!C:C,0),1,1,DAY(C3)))+IF(MONTH(B3)-MONTH(C3)>=-1,0,SUM(OFFSET(Sh1!$C$2,MATCH(A3,Sh1!A:A,0)+MONTH(B3)-MATCH("Month\Day",Sh1!C:C,0),1,MONTH(C3)-MONTH(B3)-1,31)))

The RED portion deals with the month in which the "from" date is. If both dates are in the same month, this is all that's needed.
The BLUE portion deals with the month in which the "to" date is. If the "to" date is in the same month as the "from" date, this portion equals zero.
The PURPLE portion deals with months between the "from" date and the "to" date. If the "to" date is in the same month as, or the month after the "from" date, this portion resolves to zero, otherwise it sums up whole months which fall between the "to" and the "from" dates.

 

[jasonb75]

Here's another one that works in D3 with your original layout

=SUM(INDIRECT("RegHoursImport!"&ADDRESS(MATCH(MONTH(B3),INDIRECT("RegHoursImport!$C$"&MATCH(A3,RegHoursImport!$A$1:$A$25,0)&":$C$"&(MATCH(A3,RegHoursImport!$A$1:$A$25,0)+11)),0)+(MATCH(A3,RegHoursImport!$A$1:$A$25,0)-1),MATCH(DAY(B3),RegHoursImport!$D$1:$H$1,0)+3)&":"&ADDRESS(MATCH(MONTH(C3),INDIRECT("RegHoursImport!$C$"&MATCH(A3,RegHoursImport!$A$1:$A$25,0)&":$C$"&(MATCH(A3,RegHoursImport!$A$1:$A$25,0)+11)),0)+(MATCH(A3,RegHoursImport!$A$1:$A$25,0)-1),MATCH(DAY(C3),RegHoursImport!$D$1:$H$1,0)+3)))

 

[jasonb75]

Left it too late to edit my original formula, this one has been tweaked a little so you should be able to copy it straight to B3 and fill down without editing. The last one was limited to the data range in your sample table.

=SUM(INDIRECT("RegHoursImport!"&ADDRESS(MATCH(MONTH(B3),INDIRECT("RegHoursImport!$C$"&MATCH(A3,RegHoursImport!$A:$A,0)&":$C$"&(MATCH(A3,RegHoursImport!$A:$A,0)+11)),0)+(MATCH(A3,RegHoursImport!$A$1:$A$25,0)-1),MATCH(DAY(B3),RegHoursImport!$D$1:$AH$1,0)+3)&":"&ADDRESS(MATCH(MONTH(C3),INDIRECT("RegHoursImport!$C$"&MATCH(A3,RegHoursImport!$A:$A,0)&":$C$"&(MATCH(A3,RegHoursImport!$A:$A,0)+11)),0)+(MATCH(A3,RegHoursImport!$A:$A,0)-1),MATCH(DAY(C3),RegHoursImport!$D$1:$AH$1,0)+3)))

 

[MagiCat]

Thanks gardnertoo, works almost perfectly!!! Changing Sh1!$C$2 to Sh1$C$1 in 3 locations fixed things as far as I could see. Could you let me know if this change should work in all cases? I tested as many as I could think of, but you're obviously really good at this. Couldn't do the all 1 column thing because we can quickly get over 250 employees rather quickly and run out of columns (and there's no way our company's going to spring for 2007 no matter how much I beg).

Jason, didn't try yours because I got Gardnertoo's to work so quickly! Are there any advantages to your formula with regard to speed of recalcs? I'm not sure how INDIRECT and ADDRESS work with recalculating (never used those before.)

Here's a formula for cell D3 that works with the data in the format you have:

=SUM(OFFSET(Sh1!$C$2,MATCH(A3,Sh1!A:A,0)+MONTH(B3)-1-MATCH("Month\Day",Sh1!C:C,0),DAY(B3),1,(MIN(C3-B3+1,31))))+(MONTH(C3)>MONTH(B3))*SUM(OFFSET(Sh1!$C$2,MATCH(A3,Sh1!A:A,0)+MONTH(C3)-1-MATCH("Month\Day",Sh1!C:C,0),1,1,DAY(C3)))+IF(MONTH(B3)-MONTH(C3)>=-1,0,SUM(OFFSET(Sh1!$C$2,MATCH(A3,Sh1!A:A,0)+MONTH(B3)-MATCH("Month\Day",Sh1!C:C,0),1,MONTH(C3)-MONTH(B3)-1,31)))

 

[jasonb75]

No probs, I couldn't see quite how Gardner's formula worked so I just tried it and it seemed to come up with some odd results, looks like you found the cause tho

 

[MagiCat]

No probs, I couldn't see quite how Gardner's formula worked so I just tried it and it seemed to come up with some odd results, looks like you found the cause tho

Took me a little bit to figure it out but I'm glad I did (I was just subtracting 1 in random places until I figured it out). Of course, I have a lot more invested in getting this right than others so of course I'm going to work on it until I get it.

 

[jasonb75]

Shame I didn't get my formula up first, maybe you would have tried that one then and found it worked straight out of the box (wishful thinking, but it does happen occasionally lol).

From what I can see so far Gardnertoo's formula seems to sum up the hours in a couple of small chunks then sum them together, still trying to make sense of it so I can learn from it

 

[gardnertoo]

Changing Sh1!$C$2 to Sh1$C$1 in 3 locations fixed things

Good catch, I had my copy of your sample data shifted down one row by mistake, so the change from $C$2 to $C$1 is correct, and should work everywhere.