# Hacking stuff together with Google Spreadsheets: A simple electronic voting system

Next week I’ll be presenting at [Dev8eD (A free event for building, sharing and learning cool stuff in educational technology for learning and teaching!!)](http://devcsi.ukoln.ac.uk/upcoming-events/dev8ed/) doing a session on ‘Hacking stuff together with Google Spreadsheets’ – other online spreadsheet tools are available. As part of this session I’ll be rolling out some new examples. Here’s one I’ve quickly thrown together to demonstrate `UNIQUE` and `FILTER` spreadsheet formula. It’s yet another example of me [visiting the topic of electronic voting systems (clickers)](https://hawksey.info/blog/category/evs/). The last time I did this it was [gEVS – An idea for a Google Form/Visualization mashup for electronic voting](https://hawksey.info/blog/2010/12/gevs-google-visualization/), which used a single Google Form as a voting entry page and rendering the data using the Google Chart API on a separate webpage. This time round everything is done in the spreadsheet so it makes it easier to use/reuse. [Below is a short video](http://www.youtube.com/watch?v=wkEhCcia1gA) of the template in action followed by a quick explanation of how it works.

Here’s the: **\*\*\* [Quick Clicker Voting System Template](https://docs.google.com/spreadsheets/d/1VvqnQXFvdoW2sxGtopQ9od6bUcM1xG45Ds6gGWjEJK4/copy) \*\*\***

The instructions on the ‘Readme’ tell you how to setup. If you are using this on a Google Apps domain (Google Apps for Education), then it’s possible to also record the respondents username. ![record the respondents username](https://hawksey.info/wp-content/uploads/2012/05/image6.png "record the respondents username")

All the magic is happening in the FILTERED sheet. In column A cell 1 (which is hidden) there is the formula `=UNIQUE(SORT(LOWER(ALL!C2:C)))`. This returns a list of unique questions ids from the ALL sheet. If you now highlight cell D2 of the FILTERED sheet and select Data > Validation you can see these values are used to create a select list. ![create a select list](https://hawksey.info/wp-content/uploads/2012/05/image7.png "imagecreate a select list")

The last bit of magic is in cells `D4:D8`, which uses the COUNTIFS function to filter and count the response values based on the selected question ID: `=COUNTIFS(ALL!C:C,$D$2,ALL!D:D,C4)`

Simple, yes?