Google-sheets – A chart with multiple series encoded by comma-separated names

google sheetsgoogle-sheets-charts

I have the following table

enter image description here

I need a graph with dates on X axis (with the option to group by weeks/months/years), values on Y axis and lines for each name (A,B and C) representing the sum of values on the given date.

For example A is 4 on 01/01; 3 on 01/02 and 0 on 01/03…

Something like this
enter image description here

Best Answer

One approach is to separate the data into three sets using three query functions such as

=query(A:C, "select A, sum(C) where B contains 'A' group by A", 1)

But this has a drawback: the dates for which 'A' has no data are omitted instead of having 0. A modified approach is to preprocess the values with arrayformula. For example, in D2:

=arrayformula(if(iserror(find("A", B2:B)), 0, C2:C))

which outputs the content of C if "A" is found in column B, and 0 otherwise. Similarly in E2 and F2 for two other names. Finally, the query

=query(A:F, "select A, sum(D), sum(E), sum(F) where A is not null group by A", 1)

will group the values in D, E, F by date, with this output: 

+-----+---+---+---+
| 1/1 | 4 | 9 | 8 |
| 1/2 | 3 | 1 | 3 |
| 1/3 | 0 | 2 | 2 |
+-----+---+---+---+
Related Topic