• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summing Dates Only When Continuous

A

Alex Legge

New Member
This is a tricky problem and may not even be possible in Excel, but I figured this forum would be my best shot.

Below is a screenshot of my data with column AE unfinished (I manually entered the first few values, but the dataset is gigantic). I'm trying to devise a way to complete it. Basically, AE should show the sum of AD values within a stretch of dates only when it is continuous.

For instance, AB2:AC4 is a continuous stretch of dates from 7/1/2010-11/7/2004, but AB5 represents a new continuous stretch. The sum of AD2:AD4 is all within the continuous stretch, and I want that sum to be listed in each corresponding cell of AE. In other words, AE values are the total number of days within that continuous stretch of dates.

Any ideas?? And if this isn't possible in Excel, does anyone have an idea of how to solve it via other means?

Sincere thanks for your help and suggestions.

Excel Example.JPG
 
The helper columns are no problem - thanks!

What about getting the same 'final output' into the other cells that correspond to the same continuous stretches?

Eventually, the goal is to delete rows that are not part of a continuous stretch >182 days long.
 
Hi Alex ,

See your file now.

Note that the formula in the Final Output column uses the end point as one cell after the last row of data ; thus , in your file , row 30 is the last row with data , and the formula uses $H$31 as the end point , since the MATCH function requires an array as the second parameter , and H30:$H$30 does not work.

Narayan
 

Attachments

Fantastic. Problem solved! Thanks again for your help; I'm very impressed and will definitely be visiting this forum if I have any other excel issues. *applause*
 
You must log in or register to reply here.
Back
Top