Cumulative Line Chart Over Time Period

[Chets]

Hi All

I am trying to build a line chart that shows me cumulative total of planned vs actual servers over time. Attached sample spreadsheet shows rows of server names each with planned date and actual date. I would like to show for year/month the number of servers planned and actual cumulatively. The horizontal axis needs to be year/month and vertical axis should be number of servers.

Chart should be similar to https://goo.gl/images/TXm8L3.

Thanks.

 

[Chihiro]

I'd restructure your data. See attached.

Use PivotTable to summarize your data, using Date without grouping (you may need to right click and ungroup) as Row label, and Count of Server Name as values.

Then right click on value field and "Show Value As..." -> Running Total In.

Base field set to Date.

 

[Peter Bartholomew]

Something like this?

 

[Peter Bartholomew]

This version is the same functionally as the previous #3 (if that is based upon a misunderstanding of what is required then so is this). What is different is that the calculation for the chart is done entirely within named formulae to bypass issues of extending or reducing array lengths (the helper ranges are no longer needed) as the data is changed.

As a comment, this would have been so much easier if I had SEQUENCE, SORT and FILTER available to use. Can't wait!