# AP style poll in Google Sheets

I'm trying to set up an AP Top 25 style poll via Google Sheets ( https://en.wikipedia.org/wiki/AP_Poll ). What I want to do is list the voters' choices on one sheet, and have them totaled and sorted on another. I'll set up a set of examples to describe what I'm trying to do.

Under this example voting scheme, a school gets 5 points for a 1st place vote, four for 2nd, three for 3rd place, two points for 4th place, and one point for 5th. Let's say I manually enter three sets of votes as such:

Under this setup, Alabama should have 11 points, Georgia 8 points, Clemson 10 points, Ohio State 9 points, and Washington 7 points. What I want to do is have another tab of the same sheet calculate the point totals of each school based on which cell they're placed in, display that point total, and sort itself from the greatest number of points to least number of points.

Now, I already understand some basic google sheets functions like sum, product, etc. What I'd like to do is have the "totals" tab populate automatically based on whats entered on the votes tab, without having to type out each individual school. So I need a way for the "totals" tab to look at the first entry in B2 on the votes tab, see "Alabama", make its own entry for "Alabama" on the totals tab, and then add the score for each subsequent entry for "Alabama" on the votes tab based on what column position its in in its respective row. Then do the same for any other unique school entry on the votes tab. Am I making sense?

assuming your dataset is in this format at same columns under `Sheet1`:

you can add +1 sheet with calculations where:

A2: `=ARRAYFORMULA(IF(Sheet1!B2:B="";;Sheet1!B2:B&"_"&ROUNDUP(COLUMN(Sheet1!B2:B)+3)))`
B2: `=ARRAYFORMULA(IF(Sheet1!C2:C="";;Sheet1!C2:C&"_"&ROUNDUP(COLUMN(Sheet1!C2:C)+1)))`
C2: `=ARRAYFORMULA(IF(Sheet1!D2:D="";;Sheet1!D2:D&"_"&ROUNDUP(COLUMN(Sheet1!D2:D)-1)))`
D2: `=ARRAYFORMULA(IF(Sheet1!E2:E="";;Sheet1!E2:E&"_"&ROUNDUP(COLUMN(Sheet1!E2:E)-3)))`
E2: `=ARRAYFORMULA(IF(Sheet1!F2:F="";;Sheet1!F2:F&"_"&ROUNDUP(COLUMN(Sheet1!F2:F)-5)))`

which will assign points based on your request:

``````1st place = 5 points
2nd place = 4 points
3rd place = 3 points
4th place = 2 points
5th place = 1 point
``````

next step is sorting all of it under one column with formula:

F2: `=SORT({A2:A; B2:B; C2:C; D2:D; E2:E}; 1; 1)`

then you need to split the whole column into two columns (one for participants and another for points) with a formula:

G2: `=IFERROR(ArrayFormula(SPLIT(F2:F;"_";1;1));)`

in the next step, you need to create a feeding ground for `=QUERY` with a simple formula:

I2: `=UNIQUE(G2:G)`

now you can feed `=QUERY` from a range `G2:H` with unique values from `I column` by using a formula (which needs to be dragged all the way down):

J2: `=IFERROR(QUERY(\$G\$2:\$H;"select G, sum(H) where G = '"&I2&"' group by G label sum(H)''");)`

and now you can use one more `=QUERY` formula to get your desired sorted data:

`=QUERY(calculations!J2:K;"select * where J is not null order by K desc")`

summary: