0

I have time-series data that follows no predictable entries. For example as below.

Date Units
01 Jan 2022 1
05 Jan 2022 2
08 Jan 2022 1
30 Jan 2022 1
04 Feb 2022 1
10 Feb 2022 1
13 Feb 2022 2
28 Feb 2022 1
07 Mar 2022 3
14 Mar 2022 1
23 Mar 2022 1
28 Mar 2022 3

What I want to do here is I want to add a sum to particular date. Expected output looks something below this.

Date Total Units till that date
01 Jan 2022 ?
15 Jan 2022 ?
01 Feb 2022 ?
15 Feb 2022 ?
01 Mar 2022 ?
15 Mar 2022 ?

Now, I don't want to add all the dummy entries for missing dates in input table (because data is going to be huge)

Is there any way I can create a summary like this?

2 Answers 2

1

https://docs.google.com/spreadsheets/d/1jQ4CFfk0J0QjKLUmRYtQZms4Of9jaFDAeWNW56FeBF0/edit?usp=sharing

My solution works by using SUMIF. I'm not sure whether you want that day to be included in the count, but if you wanted to do so, just change the criterion to "<=" & E2.

1
1

This is my try, hope I understand you well Try test sheet

2
  • Thanks for the answer, Daniele. I didn't understand the point of the table on the right side. Why is it required? Can we skip it? Apr 24 at 5:08
  • That column (the Z) is used to automatically calculate 15-day periods (mid-month and end of month) so that you don't have to think about updating column C ... the update will happen automatically... BTW you can place that calculazion in another column, if you prefer
    – Daniele
    Apr 24 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.