4

I need to create a stacked column chart with Date aggregated on X-axis, and the bars with multiple (dynamically formed?) Category series.

All of the examples out there demonstrate how to create the stacked graphs with the separate columns for each category. Is it possible to achieve the goal without the redundancy of creating another table with categories inlined into dozens of columns? I would really love to avoid that.

I would appreciate the help

Table in maintenance

player0
  • 124,011
  • 12
  • 67
  • 124
Bexultan Myrzatay
  • 1,105
  • 10
  • 17
  • you can have your ABC columns as are and then one formula that will distribute categories into separate columns, but without that formula it is not possible – player0 Apr 18 '22 at 10:57
  • 1
    Could you provide the formula? Not sure how much time and stress it will cost me at this point. I am a noob in sheets – Bexultan Myrzatay Apr 18 '22 at 11:04

1 Answers1

5

try:

=QUERY(A2:C, "select A,sum(B) where A is not null group by A pivot C")

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Worked like a charm. Thank you! Could you elaborate on what the formula does? It would solidify your answer and might be helpful for the more demanding students who gets upon this question. Have a wonderful week! – Bexultan Myrzatay Apr 19 '22 at 10:56
  • @BexultanMyrzatay sure, tho there is not much to say... formula copies A column and aggregates B column if A column is not empty and then it groups data by A column while it pivots C column. that's pretty much all whats happening – player0 Apr 19 '22 at 11:05