# Thread: Get Sequence of Date then sum in sum sheet

1. ## Get Sequence of Date then sum in sum sheet

Hello Gurus,

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 2-Nov-13 and 3-Nov-13 I need to get those dates in Sum sheet like start date is 2-Nov-13 and End Date is 3-Nov-13 With the sum of values of those dates. Similarly if dates comes like 11-Nov-13 , 12-Nov-13 , 13-Nov-13 in calculation sheet, I need the start date which will be 11-Nov-13 and End date will be 13-Nov-13 in sum Sheet with sum of those 3 days.

This is quite big sheet i only attached the sample data.

2. 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

3. 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 2-nov-13 then 3-nov-13 after that there is a date break 11-nov-13 to 13-nov and then 19-nov-13 to 21-nov-13.

I need the sum of values ,start date ,end date based on date break?
sorry if i cannot clear

4. 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 ctrl-shift-enter) seems to work in B4 for the sample:

and in C4 (again confirm with ctrl-shift-enter:

Steve

5. 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 2-Nov-13 onward in the attached screen shot there is a break of sequence after 3-Nov-13, so those date range plus sum of those dates to be show in the Sum Sheet next in the screenshot 11-Nov-13 start date and end date will be 13-Nov-13. I need the date which is 11-Nov-13 just after date break of sequence and where the date break comes which is 13-Nov-13 and get the sum of those days.

Thanks

6. Have you tried the formula I have posted? That is what I believe the formulas I have posted do...

Steve

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

8. what is the problem exactly with the formula? In the sample it gave the results you listed...

Steve

9. ## The Following User Says Thank You to sdckapr For This Useful Post:

farrukh (2013-12-25)

10. Ahh sorry in the sampler i copy paste the values.Please see the attached spreadsheet what i am doing wrong.

11. 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"

12. Sdckpar many thanks worked perfectly...
Thanks for help

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•