Google-sheets – Create a pie chart using a formula

google sheetsgoogle-sheets-chartsworksheet-function

I'm using Google's monthly budget sheet to track my expenses.

I need to have a pie chart but I don't want it to be floating around in the sheet and be in a specific cell(s). So I thought a formula like Sparkline might be the answer. But is there such a formula? How can I achieve this?

Best Answer

For the moment at least, I think not. Try a pie chart using SPARKLINE and the error is:

Function SPARKLINE parameter 2 option charttype value is pie. It should be one of: 'line', 'column', 'winloss', 'bar'.

You might consider embedding an IMAGE instead but I doubt worth the bother ever were the image to be dynamic, that would be yet more bother.

Formula

=QUERY(Sheet1!A1:C9;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A PIVOT C")

then the table will appear as you want.

Explained

Here an explanation of the formula, from the inside to the outside:

the PIVOT C transposes the unique results from column C (turning rows into columns)
the GROUP BY A only filters out unique values in column A. (the same as sheet TABLE!A2)
the WHERE B IS NOT NULL ignores empty cells
the SUM(B) adds the result of the query
the SELECT A simply displays column A, as unique values

If you're only interested in individual results, then I suggest using the following formula:

=IFERROR(SUM(FILTER(DATA!$B$2:B;DATA!$C$2:C=$B$1;DATA!$A$2:A=A2));"")

Here an explanation of the formula, from the inside to the outside:

the FILTER function will retrieve the amounts for range B2:B, column B without the header, by filtering range B2:B for "No" and range C2:C for "Yes".
the SUM function will add them together.
the IFERROR will leave a blank cell if an error occurred (not result).

Screenshot

Example

See example file I've prepared, where both samples are present: SUM data based on two variables

Note

The formula in the example file is a bit different (the range is set to contain complete columns).

Google Sheets Charts – Generating Charts from Google Forms Data

You could concatenate the strings, so that the row Male | City A is represented as MaleCity A, and count the occurrences of those strings.

Given that column B contains either Male or Female, and column C contains either City A or City B, you could count males/females in each city:

Number of males in City A: =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "MaleCity A")
Number of females in City A: =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "FemaleCity A")
Number of males in City B: =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "MaleCity B")
Number of females in City B =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "FemaleCity B")

The percentage of males/females in each city can then easily be calculated.

See the example spreadsheet I set up.