1

Let us say I have data like this for a spend summary sheet:

Date Amount Category
03/31/2023 20.0 Food
03/31/2023 23.12 Coffee
03/31/2023 21.01 Movie
04/01/2023 10.0 Coffee
04/01/2023 12.0 Coffee
04/01/2023 13.12 Food
04/02/2023 10.0 Movie

I know how to get an aggregate plot by using the Date and Amount columns.

I would like a line plot of multiple lines, now for each category, within the same graph. i.e. for each category, I have a differently colored aggregate line plot. Here for eg, I'd have 3 different colored lines plot, one for food, one for coffee and one for movie, all within the same plot. I'd also like to have a line plot of all categories combined as well, in the same plot. Is such a thing possible in sheets?

Thanks

Improve this question
1

1 Answer 1

Reset to default
2

One way to do that is to pivot the data so that Food, Coffee and Movie each have their own column, like this:

=query( A1:C, "select A, sum(B) where B is not null group by A pivot C", 1 )

Then plot the result table instead of the original. Use a stacked column chart to view how total develops over time.

If you absolutely want to add a separate totals figure, use this pattern:

=let( data, query( A1:C, "select A, sum(B) where B is not null group by A pivot C", 1 ), rowsum_, lambda(row, iferror(sum(row) - single(row), "Total")), { data, byrow(data, rowsum_) } )

You can move the Total series to the Right axis so that its scale does not diminish the others, but showing totals this way is seldom as informative as using a stacked column chart.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.