Calculated field over list of values

Question

Given this table of data:

I'd like to produce this pivot table:

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!

Answer 1

={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)}}

Function References

• Query
• COUNT
• COUNTIFS

---

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

Answer 2

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

to "set it free" you can do:

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