Search code examples
arraysgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

Calculated field over list of values

Given this table of data:

original data

I'd like to produce this pivot table:

desired result

I have an inkling this can be done with the calculated field, and SUMIF, but am not able to get it to work. I think the main blocker is that I'm not able to find good documentation for what I can reference inside of a calculated field formula. My best attempt was =SUMIF(color, "RED")/SUM(), but that produced zeros.

Example table at https://docs.google.com/spreadsheets/d/16htOLbwf47Neo68iFlm9OvFVS_u2Jlc-2thhdUQwrpU/edit?usp=sharing

Any guidance appreciated!

Solution 
  • ={QUERY(A1:B25,"select A,count(A)/"&COUNT(A:A)&" where B='RED' group by A label count(A)/"&COUNT(A:A)&" 'PCT RED'");{"Grand Total",COUNTIFS(A:A,">=0",B:B,"RED")/COUNT(A:A)}}

    enter image description here

    Function References

    I think my concern here would be that with a normal pivot table it's robust against data moving around. This seems to break that by referencing specific columns

    Method pivot table you must show all color enter image description here