Results 1 to 11 of 11

20131212, 09:13 #1
 Join Date
 Jun 2011
 Posts
 188
 Thanks
 62
 Thanked 0 Times in 0 Posts
Get Sequence of Date then sum in sum sheet
Hello Gurus,
I am really stuck in my senario please help me out.
I have one calculation sheet and other is Sum sheet. In the calculation sheet dates are random suppose in the month of november 13 the date sequence is 2Nov13 and 3Nov13 I need to get those dates in Sum sheet like start date is 2Nov13 and End Date is 3Nov13 With the sum of values of those dates. Similarly if dates comes like 11Nov13 , 12Nov13 , 13Nov13 in calculation sheet, I need the start date which will be 11Nov13 and End date will be 13Nov13 in sum Sheet with sum of those 3 days.
This is quite big sheet i only attached the sample data.

20131213, 07:55 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
in D4:
=SUMIFS(Caculation!$G$4:$G40,Caculation!$B4:$B40," >="&B4,Caculation!$B4:$B40,"<="&C4)
Change the range as appropriate and copy down the column.
Steve

20131214, 01:41 #3
 Join Date
 Jun 2011
 Posts
 188
 Thanks
 62
 Thanked 0 Times in 0 Posts
Capture.PNG
Hello sdckapr,
The formula will get me the sum from calculation sheet but what about date range. For date range i need if dates are going in the sequence suppose 2nov13 then 3nov13 after that there is a date break 11nov13 to 13nov and then 19nov13 to 21nov13.
I need the sum of values ,start date ,end date based on date break?
sorry if i cannot clear

20131214, 04:57 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I don't understand. The sum uses the date range given and only calculates the dates within that range. The range can be 1 day, 2 days or any number of days.
If you want to extract the dates as well, it is more complicated since you have irregular counting and even blanks in some of the sequences. This array (confirm with ctrlshiftenter) seems to work in B4 for the sample:
=MIN(INDIRECT("Caculation!"&ADDRESS(MATCH(A4,Cacul ation!A:A,0),2)&":"&ADDRESS(MATCH(A4+1,Caculation! A:A,0)1,2)))
and in C4 (again confirm with ctrlshiftenter:
=MAX(INDIRECT("Caculation!"&ADDRESS(MATCH(A4,Cacul ation!A:A,0),2)&":"&ADDRESS(MATCH(A4+1,Caculation! A:A,0)1,2)))
Steve

20131215, 06:25 #5
 Join Date
 Jun 2011
 Posts
 188
 Thanks
 62
 Thanked 0 Times in 0 Posts
Samplescreenshot.PNG
Hello sdckapr,
Sorry i think i unable to clear my forum help.
Actually the Sum sheet will be created automatically there is no match case.
I have given the coloring to cells to make clear myself.
If the dates are going in the sequence e.g 2Nov13 onward in the attached screen shot there is a break of sequence after 3Nov13, so those date range plus sum of those dates to be show in the Sum Sheet next in the screenshot 11Nov13 start date and end date will be 13Nov13. I need the date which is 11Nov13 just after date break of sequence and where the date break comes which is 13Nov13 and get the sum of those days.
Thanks

20131215, 11:30 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Have you tried the formula I have posted? That is what I believe the formulas I have posted do...
Steve

20131220, 02:12 #7
 Join Date
 Jun 2011
 Posts
 188
 Thanks
 62
 Thanked 0 Times in 0 Posts
Thanks Sdckpar,
Exactly your post # 4 works for me . I am unable to fix the below formula to get the sum.
=SUMIFS(Caculation!$G$4:$G40,Caculation!$B4:$B40," >="&B4,Caculation!$B4:$B40,"<="&C4)

20131220, 04:26 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
what is the problem exactly with the formula? In the sample it gave the results you listed...
Steve

The Following User Says Thank You to sdckapr For This Useful Post:
farrukh (20131225)

20131225, 07:58 #9
 Join Date
 Jun 2011
 Posts
 188
 Thanks
 62
 Thanked 0 Times in 0 Posts
Ahh sorry in the sampler i copy paste the values.Please see the attached spreadsheet what i am doing wrong.

20131225, 09:29 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Try this in D4
=SUMIFS(Caculation!$G$4:$G$40,Caculation!$B$4:$B$4 0,">="&B4,Caculation!$B$4:$B$40,"<="&C4)
Copy it down the column.
Steve
PS there is not space before the greater than sign (>) and the ranges should be "locked"

20131228, 08:01 #11
 Join Date
 Jun 2011
 Posts
 188
 Thanks
 62
 Thanked 0 Times in 0 Posts
Sdckpar many thanks worked perfectly...
Thanks for help