How to tally votes in Excel?
May 4, 2008 4:28 PM Subscribe
How to tally votes in Excel?
There is an Excel spreadsheet with many categories, with many votes per category. How do I get Excel to tally up the individual votes in a singe category? Each category is column, with the various votes per category listed as rows.
Let's say there are ten rows, 1 to 10, and 2 columns, A and B.
Then in A12 (or wherever you want, actually), put "sum(a1:a10)", and in B12, put "sum(b1:b10)".
posted by Flunkie at 4:56 PM on May 4, 2008
Then in A12 (or wherever you want, actually), put "sum(a1:a10)", and in B12, put "sum(b1:b10)".
posted by Flunkie at 4:56 PM on May 4, 2008
Or, if you want to keep it easy to add rows in the future, insert a couple blank rows at the top (so that the votes are now in cells A3 through B12), and in A1, put "sum(A3:A9999)", and in B1, put "sum(B3:B9999)".
posted by Flunkie at 4:58 PM on May 4, 2008
posted by Flunkie at 4:58 PM on May 4, 2008
Please to explain. No, better yet: some sample data. (Paste some data, or mock some up, into google spreadsheets?) Columns are categories, and each row is a single vote? multiple votes? Does each cell contain a "Y/N"? a "1"? an arbitrary integer == number of votes? Text values? Are the votes freeform text, or user-provided and arbitrary? Are you just trying to sum each column?
posted by misterbrandt at 4:58 PM on May 4, 2008
=SUM(A1:A40)? Without knowing more, I would also suggest looking at the COUNTIF(), SUMIF(), etc functions. Or do some reading on the Mr. Excel site.
posted by misterbrandt at 4:58 PM on May 4, 2008
As Mr. Brandt's post indicates, I forgot that you have to put an equals sign in there.
posted by Flunkie at 5:01 PM on May 4, 2008
posted by Flunkie at 5:01 PM on May 4, 2008
Response by poster: The data is people's names (it's an employee contest)
so it might appear as this:
A1 ################ B1
Greatest Sales Starter Best Closer
Jon Harris ############Betty Wright
John Clark Stanford
Betty Betty
Clark John Harris
John Harris Clark
Moncia Gable Betty Thompson
Clark S.
John
The # are spaces, meant to indicate columns
So if we want to tally who the best Sales Starter is, who do we get excel to count up all the text fields in that column? If John, Jon Harris and John are all the same person, can we force Excel to count them as one person?
posted by jmitchell at 5:09 PM on May 4, 2008
so it might appear as this:
A1 ################ B1
Greatest Sales Starter Best Closer
Jon Harris ############Betty Wright
John Clark Stanford
Betty Betty
Clark John Harris
John Harris Clark
Moncia Gable Betty Thompson
Clark S.
John
The # are spaces, meant to indicate columns
So if we want to tally who the best Sales Starter is, who do we get excel to count up all the text fields in that column? If John, Jon Harris and John are all the same person, can we force Excel to count them as one person?
posted by jmitchell at 5:09 PM on May 4, 2008
The Countif tool will probably work best here (again seconding misterbrandts request that you post more info on your data).
posted by elkerette at 5:16 PM on May 4, 2008
posted by elkerette at 5:16 PM on May 4, 2008
If John, Jon Harris and John are all the same person, can we force Excel to count them as one person?
No. Excel is dumb. Methodical, but dumb. How many rows of votes are we talking about here? Would it be problematic to normalize the data yourself (manually) first?
posted by misterbrandt at 5:17 PM on May 4, 2008
No. Excel is dumb. Methodical, but dumb. How many rows of votes are we talking about here? Would it be problematic to normalize the data yourself (manually) first?
posted by misterbrandt at 5:17 PM on May 4, 2008
Response by poster: We're talking about 600 rows of data and about 150 columns. Normalizing would take a while. Excel can do find and replace throughout the entire sheet, yes?
posted by jmitchell at 5:20 PM on May 4, 2008
posted by jmitchell at 5:20 PM on May 4, 2008
Response by poster: Hmm, making a PivotTable Report seems to collect all the data for the selected column. Is there a way to show the individual rows from the column, it's just showing the total number of column data i.e. how many people actually voted in that category/column.
posted by jmitchell at 5:30 PM on May 4, 2008
posted by jmitchell at 5:30 PM on May 4, 2008
I agree with the other posters that you need to make the names etc consistent, otherwise you'll have problems..
Once you do that, you build a results table down the bottom with two columns, one with the names (A) and one with the the results (B). You then use countif to tally up the number of items that match each name. So the cells would be like (where | splits columns, and the names are in column A, starting with 110):
John Smith | =countif(B1:B100, A110)
Mike Jones | =countif(B1:B100, A111)
etc...
I usually also put a total field down the bottom which adds the counts, plus a count column which counts them all.. That way, if they don't match, I know I've missed something (ie. a name is spelt wrong)! Code for that would be (where the counts are in the B columns 110 and 111):
TOTAL | =sum(B110:B111)
COUNTS | =sum(B1:B100)
posted by ranglin at 5:32 PM on May 4, 2008
Once you do that, you build a results table down the bottom with two columns, one with the names (A) and one with the the results (B). You then use countif to tally up the number of items that match each name. So the cells would be like (where | splits columns, and the names are in column A, starting with 110):
John Smith | =countif(B1:B100, A110)
Mike Jones | =countif(B1:B100, A111)
etc...
I usually also put a total field down the bottom which adds the counts, plus a count column which counts them all.. That way, if they don't match, I know I've missed something (ie. a name is spelt wrong)! Code for that would be (where the counts are in the B columns 110 and 111):
TOTAL | =sum(B110:B111)
COUNTS | =sum(B1:B100)
posted by ranglin at 5:32 PM on May 4, 2008
I was gonna suggest the "CountIF" function, however because of the input variability (the fact that "John Stanford" could be voted for by saying John, Stanfrod, John S. or J. Stanford) your gonna have problems.
If you knew that all votes for "John Stanford" would include either the first or last name (say the options were only "John Stanford", or "J. Stanford" but NOT just "John" or "John S.") then you could have a John Stanford vote total cell with the function:
=CountIF(RANGE,"*Stanford*")
where RANGE is the column the votes for him are in. the criteria with TEXT surrounded by stars (*) will find any cell that has "Stanford" even if it is "J.Stanford" or "McStanforder". So if you have two similar last names (like "Buck" and "Buckley") you will again have problems as Buckley will get all his votes but Buck will get his votes AND Buckley's vote in the tally.
So whatever you choose, do some basic double checking to ensure erroneous vote tallies did not occur.
posted by DetonatedManiac at 5:56 PM on May 4, 2008
If you knew that all votes for "John Stanford" would include either the first or last name (say the options were only "John Stanford", or "J. Stanford" but NOT just "John" or "John S.") then you could have a John Stanford vote total cell with the function:
=CountIF(RANGE,"*Stanford*")
where RANGE is the column the votes for him are in. the criteria with TEXT surrounded by stars (*) will find any cell that has "Stanford" even if it is "J.Stanford" or "McStanforder". So if you have two similar last names (like "Buck" and "Buckley") you will again have problems as Buckley will get all his votes but Buck will get his votes AND Buckley's vote in the tally.
So whatever you choose, do some basic double checking to ensure erroneous vote tallies did not occur.
posted by DetonatedManiac at 5:56 PM on May 4, 2008
Use =COUNTA(range), where range is the range of cells in the first column containing the votes (e.g. =COUNTA(A2:A34) would count votes in column A for rows 2 to row 34). Then drag the formula across all columns with votes and the formula will self-adjust to each column. the COUNTA function counts all cells in a range that contain alphanumeric contents.
If you insert a row above the data cells, you can put the formulae along the top of the worksheet.
posted by Susurration at 6:19 PM on May 4, 2008
If you insert a row above the data cells, you can put the formulae along the top of the worksheet.
posted by Susurration at 6:19 PM on May 4, 2008
Response by poster: Ok, making a PivotTable of the column shows me all the "answers" in the category, letting me see all the variations of employee names. From there I can do Find and Replace and refresh the table. That seems to be the quickest and most brain dead way to do it.
posted by jmitchell at 6:22 PM on May 4, 2008
posted by jmitchell at 6:22 PM on May 4, 2008
Response by poster: Hmm, is there anyway to do a find and Replace in the main sheet, without Excel taking you back to the first column of the sheet? Using Excel 2008 on the Mac.
posted by jmitchell at 6:28 PM on May 4, 2008
posted by jmitchell at 6:28 PM on May 4, 2008
Response by poster: First up, thank you to everyone who has taken the time to answer. This for work, somethign the boss dumped on me and I'm frantically trying to figure out Excel.
Now lets scratch all the other questions and just go this final (I promise!)one:
I've made a PivotTable Report, which lists all the data in a particular column, with their variations. From here, it seems like I could easily just do a Find and Replace in the Workbook, which I've tried but weird things start happening.
Specifically: While in the PivotTable I try to do a Find and Replace, where Find is "Jon Tomson" and I want to replace it with "Jon Thompson".
I set the search to be within the Workbook, since there are probably other misspellings and I could catch them all in one swoop.
Search is by "rows", "exact cas" is off and "Find Entire Cells Only" is on.
When I hit replace I get this error: "Replacement creating duplicate item or field name ignored"
WTF? I don't understand why Excel won't just do the find and replace and I'm soooo close to having this licked and being the company hero for day. Help please!
posted by jmitchell at 6:53 PM on May 4, 2008
Now lets scratch all the other questions and just go this final (I promise!)one:
I've made a PivotTable Report, which lists all the data in a particular column, with their variations. From here, it seems like I could easily just do a Find and Replace in the Workbook, which I've tried but weird things start happening.
Specifically: While in the PivotTable I try to do a Find and Replace, where Find is "Jon Tomson" and I want to replace it with "Jon Thompson".
I set the search to be within the Workbook, since there are probably other misspellings and I could catch them all in one swoop.
Search is by "rows", "exact cas" is off and "Find Entire Cells Only" is on.
When I hit replace I get this error: "Replacement creating duplicate item or field name ignored"
WTF? I don't understand why Excel won't just do the find and replace and I'm soooo close to having this licked and being the company hero for day. Help please!
posted by jmitchell at 6:53 PM on May 4, 2008
you should try doing the search and replace before creating the pivot table.
posted by i love cheese at 6:56 PM on May 4, 2008
posted by i love cheese at 6:56 PM on May 4, 2008
Best answer: Why not just manually clean up the pivot table?
Start with your pivot table with its messy variations. You can't edit the entries in the pivot table, so you have to copy and paste it. But if you copy the entire thing, you just get another pivot table you can't edit. So, select everything except the top row of that table (is there a row that's a title, or maybe the one with the headings - the very top one). Hit copy. Move to somewhere else on that worksheet and hit paste.
Now, just go through and manually group all your Johns and Stanfords and whatnot. Sum them up under one label (J. Sanford or John Sanford or whatever). (On a PC? Select the cells you want to move. Hit Ctrl X to cut. Move to where you want to insert that row. Hit Ctrl Shift + to insert them. Use Ctrl - to delete the now-blank cells. To sum the grouped rows, I'd just select the cells with numbers, look in the lower right hand corner of the screen to see their total, write that down, then replace one cells' total, then delete the extraneous rows, again using Ctrl -.)
posted by salvia at 7:42 PM on May 4, 2008
Start with your pivot table with its messy variations. You can't edit the entries in the pivot table, so you have to copy and paste it. But if you copy the entire thing, you just get another pivot table you can't edit. So, select everything except the top row of that table (is there a row that's a title, or maybe the one with the headings - the very top one). Hit copy. Move to somewhere else on that worksheet and hit paste.
Now, just go through and manually group all your Johns and Stanfords and whatnot. Sum them up under one label (J. Sanford or John Sanford or whatever). (On a PC? Select the cells you want to move. Hit Ctrl X to cut. Move to where you want to insert that row. Hit Ctrl Shift + to insert them. Use Ctrl - to delete the now-blank cells. To sum the grouped rows, I'd just select the cells with numbers, look in the lower right hand corner of the screen to see their total, write that down, then replace one cells' total, then delete the extraneous rows, again using Ctrl -.)
posted by salvia at 7:42 PM on May 4, 2008
This is a guess I don't have time to check out but I don't think you can do find and replace in the pivot table. I thought that the pivot table reflects and organizes the data in the spreadsheet - therefore, I've always used find and replace in the original data in the worksheet, not in the derived pivot table.
posted by jasper411 at 7:53 PM on May 4, 2008
posted by jasper411 at 7:53 PM on May 4, 2008
Response by poster: Thanks you guys, for all your help!
posted by jmitchell at 1:58 PM on May 6, 2008
posted by jmitchell at 1:58 PM on May 6, 2008
Response by poster: salvia inparticular, that was bloody brilliant.
posted by jmitchell at 2:02 PM on May 6, 2008
posted by jmitchell at 2:02 PM on May 6, 2008
This thread is closed to new comments.
posted by salvia at 4:54 PM on May 4, 2008