Sum based on series Date

Utsav

Board Regular
Joined
Jun 1, 2006
Messages
170
Hi,


I have got a series of data from which i want some output.

4-Jan-08 30
5-Jan-08 20
6-Jan-08 20
7-Jan-08 200
8-Jan-08 400
9-Jan-08 2460
10-Jan-08 2601
11-Jan-08 5790
12-Jan-08 2110
14-Jan-08 3181
15-Jan-08 4100
16-Jan-08 5511
17-Jan-08 2680
18-Jan-08 2200
19-Jan-08 1555
21-Jan-08 20398
22-Jan-08 33216
23-Jan-08 6218
24-Jan-08 20870
25-Jan-08 10952
28-Jan-08 7860
29-Jan-08 960
30-Jan-08 26440
31-Jan-08 43129
1-Feb-08 1030
4-Feb-08 8660
5-Feb-08 10800
6-Feb-08 17991
7-Feb-08 7100
8-Feb-08 15750
9-Feb-08 16756
11-Feb-08 13205
12-Feb-08 11450
13-Feb-08 9145
14-Feb-08 8623
15-Feb-08 5991
16-Feb-08 2240
18-Feb-08 16980
19-Feb-08 8855
20-Feb-08 8920
21-Feb-08 11000
22-Feb-08 6370
23-Feb-08 13299
25-Feb-08 20230
26-Feb-08 39780
27-Feb-08 11040
28-Feb-08 27115
29-Feb-08 50856
3-Mar-08 2000
4-Mar-08 2500
5-Mar-08 3180
6-Mar-08 3500
7-Mar-08 3790
8-Mar-08 1413
10-Mar-08 3720
11-Mar-08 6704
12-Mar-08 3125
13-Mar-08 7868
14-Mar-08 6021
15-Mar-08 33240
17-Mar-08 11090
18-Mar-08 19652
19-Mar-08 13540
20-Mar-08 18340
21-Mar-08 6550
22-Mar-08 200
24-Mar-08 5371
25-Mar-08 31240
26-Mar-08 19755
27-Mar-08 21220
28-Mar-08 12485
29-Mar-08 30541
31-Mar-08 55939
2-Apr-08 200
3-Apr-08 200
4-Apr-08 2500
5-Apr-08 800
7-Apr-08 1095
8-Apr-08 5480
9-Apr-08 7020
10-Apr-08 2370
11-Apr-08 18112
12-Apr-08 28186
14-Apr-08 1300
15-Apr-08 5145
16-Apr-08 7103
17-Apr-08 11210
18-Apr-08 4660
19-Apr-08 6780
21-Apr-08 11010
22-Apr-08 7401
23-Apr-08 4319
24-Apr-08 13400


in another sheet i have got some dates like this



15-Jan-08


24-Jan-08


21-Feb-08



I want the sum data for 3,4 and 5 days before the date mentioned date.

Like if i have 15 jan
then I want sum of data from 10-14 jan,11-14 jan and 12-14 jan.It will be 5days ,4 days and 3 days.

please help

Utsav
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
=SUMIF(Sheet1!A:A,">=" & Sheet2!A1-5,Sheet1!B:B)-SUMIF(Sheet1!A:A,">=" & Sheet2!A1,Sheet1!B:B)

Where Sheet2!A1 holds your date of interest. Amend the 5 to whatever date span is required (the above does 10-14th for example, 4 would be 11-14th etc).
 

Utsav

Board Regular
Joined
Jun 1, 2006
Messages
170
Dear Richard Can u please expalin me the formula as I am not getting the desired result.

regards

Utsav
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,650
Members
414,083
Latest member
Mrsash

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top