Three Google Sheets' data graphs (pie charts) in one graph

Question

I have three google sheets of data in one spreadsheet. these sheets containing the data of ABC Town students. ABC Town has 3 Streets called Street 1, Street 2 and Street 3.

• Street 1 has 10 Students in different grades.
• Street 2 has 6 Students in different grades.
• Street 3 has 2 Students in different grades.

Every sheet has Students Grade summary Pie Graph. Now What I need is, I have to combine these three pie charts into one chart to get a final summary of three sheets. how can I do this? Please.

This is my spreadsheet: https://docs.google.com/spreadsheets/d/1NmCSRPaoGCpuyfxv24z2-M8TNktBjHNt7BoaMF6LCJY/edit?usp=sharing

explained in image:

Answer 1

or maybe you just want to combine all 3 charts (?) like:

demo spreadsheet

Answer 2

if you map out your data the right way you can create pie chart with sub-slices for each of your three pie charts and then you will just layer them. here is an example:

---

• your dataset of A1:C is not in "good" shape for creating a chart, so first, you will need to re-shape it by adding few formulas in auxiliary columns which you can hide when done

• in cell E1 paste this formula and create a base pie chart. this will create even ratio between Libaries. select primary color for each libary and maximze chart style

  ={"Libaries"\""; {TRANSPOSE(SPLIT(REPT(1&" "; 
    COUNTA(UNIQUE(FILTER(B2:B; B2:B<>""))));" "))\ 
    UNIQUE(FILTER(B2:B; B2:B<>""))}}

• if you got #ERROR! paste this into E1 cell:

  ={"Libaries",""; {TRANSPOSE(SPLIT(REPT(1&" ", 
    COUNTA(UNIQUE(FILTER(B2:B, B2:B<>""))))," ")), 
    UNIQUE(FILTER(B2:B, B2:B<>""))}}

• paste this formula in G1 cell to create labels:

  =ARRAYFORMULA(SPLIT(JOIN("×"; 
   TRANSPOSE(REPT(UNIQUE(FILTER(B2:B&"×"; B2:B<>"")); 2))); "×"))

• then paste this formula in G2 to create dataset for 2nd chart:

  =ARRAYFORMULA(QUERY(TO_TEXT($A$2:$C); 
   "select count(Col3), Col3 
    where Col1 is not null and Col2='"&G1&"'
    group by Col3 
    label count(Col3)''"; 0))

• after this you will need correction formula which can correct position of the first set of pie slices. paste this in G6 cell and create 2nd pie chart from range G2:H. then play with colors and set color of the correction pie on None

  =IF(SUM(ARRAYFORMULA(QUERY(TO_TEXT($A$2:$C); 
   "select count(Col3) 
    where Col1 is not null and Col2='"&G1&"'
    group by Col3 
    label count(Col3)''"; 0)))>3;
   SUM(ARRAYFORMULA(QUERY(TO_TEXT($A$2:$C); 
   "select Col3, count(Col3) 
    where Col1 is not null and Col2='"&G1&"'
    group by Col3 
    label count(Col3)''"; 0)))*2; 6)

• when done, overlay 1st chart with 2nd chart
• then paste this formula in i4 cell (this will be dataset for 3rd chart)

  =ARRAYFORMULA(QUERY(TO_TEXT($A$2:$C); 
   "select count(Col3), Col3 
    where Col1 is not null and Col2='"&I1&"'
    group by Col3 
    label count(Col3)''"; 0))

• next you need correction again. this time twice. paste this formula in i2 and i7

  =SUM(ARRAYFORMULA(QUERY(TO_TEXT($A$2:$C), 
   "select Col3, count(Col3) 
    where Col1 is not null and Col2='"&I1&"'
    group by Col3 
    label count(Col3)''", 0)))

• now you can construct 3rd pie chart from range i2:J. again play with colors and hide correction slices. when done overlay it on top of 1st and 2nd chart
• when done paste this formula in K4 cell (this will be dataset for the 4th pie chart)

  =ARRAYFORMULA(QUERY(TO_TEXT($A$2:$C); 
   "select count(Col3), Col3 
    where Col1 is not null and Col2='"&K1&"'
    group by Col3 
    label count(Col3)''"; 0))

• and again you need to correct the position with this formula pasted in K2 cell

  =IF(SUM(ARRAYFORMULA(QUERY(TO_TEXT($A$2:$C); 
   "select count(Col3) 
    where Col1 is not null and Col2='"&K1&"'
    group by Col3 
    label count(Col3)''"; 0)))>3;
   SUM(ARRAYFORMULA(QUERY(TO_TEXT($A$2:$C); 
   "select Col3, count(Col3) 
    where Col1 is not null and Col2='"&K1&"'
    group by Col3 
    label count(Col3)''"; 0)))*2; 6)

• create 4th pie chart from range K2:L, play with colors, hide correction slice, possition it on all previous charts
• if you want to put up some labels then you can insert drawings and overlay it once again

---

demo spreadsheet