CODEWITHSUNDEEP
chartsgoogle-sheets
Crystark
Crystark

Reputation: 4246

How to create a chart of cumulative sums per date from a chart of sums per date?

I have that kind of data in a google spreadsheet:

      | Day 1 | Day 2 | Day 3
Marc  |  10   |  5    |  8
Amy   |   -   |  15   |  3

What I would like is a chart that shows that marc had a total of 10 on day 1, 15 on day 2 and 23 on day 3 and Amy didn't exist on day 1 then had 15 on day 2 then 18 on day 3.

The ideal would be an automated solution that generates directly the chart but I guess i could extract some data to an other sheet and use for my chart. For instance:

      | Day 1 | Day 2 | Day 3
Marc  |  10   |  15   |  23
Amy   |   -   |  15   |  18

If that's the only solution, how would I generate such a table automatically based on the input of my first table knowing that new names may be added and each day a column will be added ?

Thanks

Upvotes: 6

Views: 24139

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18727

Simple way

Just make plot with bars and see cumulative sums in chart. You won't see exact number though:

1

Hard Way

Or prepare another table with new calculated sums. Suppose, your data is placed on sheet \1/. Add another sheet and paste formulas:

  • ="1!R2C2:"&"R"&COUNTA('1'!A:A)+1&"C"&counta('1'!1:1)+1 in cell A1 to count work range
  • ={'1'!B1:1} in cell B1 to copy 'Days' labels.
  • ={'1'!A2:A} in cell A2 to copy names

And finally paste this hard formula in cell B2:

=mmult(ArrayFormula(MMULT(ArrayFormula(row(INDIRECT(A1,0))^0),SPLIT(ArrayFormula(CONCATENATE("-"&INDIRECT(A1,0))),"-"))*ArrayFormula(--(SPLIT(ArrayFormula(CONCATENATE("-"&if(COLUMN(INDIRECT(A1,0)),row(INDIRECT(A1,0))))),"-")=ArrayFormula(row(OFFSET('1'!A2,,,COUNTA('1'!A:A))))))),ArrayFormula(--(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE("-"&if(row(INDIRECT(A1,0)),COLUMN(INDIRECT(A1,0))))),"-"))<=ArrayFormula(COLUMN(OFFSET('1'!B1,,,1,COUNTA('1'!1:1)))))))

Then plot your new calculated data to make something like this:

2

Note that I changed "-" by 0 to make data look like numbers.

Also zero's are listed as empty string ''. This is made by custom number format: 0;0;

Please, look at Working example

Upvotes: 6

Related Questions