Summarize Dates within Range

[Associate_1121]

Hello,

I am having trouble figuring out how to do this. Essentially, I have changing ranges in one column. Every individual date has a count as either a 0 or a 1. I want to sum all of the counts within the selected range. For example, if the range is Jan 1-Jan 5, then the new column would give a total of 3 in the attached picture.

 

[Associate_1121]

Yes, everything in the picture is in the input file. The range is the current date +1.5 years. The output will need to look through all data within that range and give a summary of the count.

[Associate_1121]

This is the full input file.

[CharlieS]

Since these are all 1.5 year ranges, any date range entered should be contained within that meaning all records should be summed every time. In case that changes, I've built out the attached solution that should give you all the necessary tools to get this done.

[Associate_1121]

The date range changes because the start date is different for each row. Since it moves up every day, there needs to be different sums for the different ranges. 

For example, on January 1, the next 1.5 years should be summed. However, on January 2, January 1 should be excluded.

[jdunkerley79 · Accepted solution]

If I have understood correctly, you want the total of Count when Start Date is in the Range specified.

If this is correct, I suggest:

First create a running total

Next convert so a complete sequence of dates (start and end)

Make it unique in case a date in both start and end (making sure the union put start before end)

Use a multi-row to copy down the running totals

You can then join to get the end date running total

Finally a formula tool to compute totals

Hope this is what you meant 

Sample attached