# Adding Cells in Date Range

#### MagiCat

##### Board Regular
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
ABCDEFGH
1EmployeeNumberEmployeeNameMonth\Day12345
2123456WASHINGTON,GEORGE108.888.608.98
3208.538.658.658.97
4300000
5400000
6500000
7600000
8700000
9800000
10900000
111000000
121100000
131200000
14251833LINCOLN,ABE109.987.5209.13
15207.938.526.538.58
16300000
17400000
18500000
19600000
20700000
21800000
22900000
231000000
241100000
251200000
RegHoursImport

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Is there a reason you can't put the data in column format? If you can, here's a quick formula:
Book1
ABCD
1from:1/6/2009
2to:1/18/2009
3
4hours5943
5emp num2345
6emp nameabe lincgeo wash
71/1/200916
81/2/200926
91/3/200931
101/4/200971
111/5/200973
121/6/200901
131/7/200953
141/8/200967
151/9/200966
161/10/200941
171/11/200932
181/12/200931
191/13/200955
201/14/200977
211/15/200950
221/16/200940
231/17/200967
241/18/200953
251/19/200913
Sheet1

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.

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

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.

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

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

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.

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

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.

Replies
2
Views
110
Replies
2
Views
144
Replies
4
Views
260
Replies
8
Views
347
Replies
10
Views
221

1,203,485
Messages
6,055,686
Members
444,807
Latest member
RustyExcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back