How to Tally Votes in Excel (4 Suitable Methods)

Looking for ways to know how to tally votes in Excel? Then, this is the right place for you. When an election happens in our society, we can easily tally those votes in Excel using some methods. Here, you will find 4 different step-by-step explained ways to tally votes in Excel.

How to Tally Votes in Excel: 4 Suitable Methods

Here, we have a dataset containing the Name, Age, and Vote of some citizens. We will show you how to tally votes using this dataset.

1. Using COUNTIF Function to Tally Votes in Excel

In the first method, we will use the COUNTIF function to tally votes in Excel. Using the COUNTIF function we can count the number of cells that meet a criterion.

Follow the steps given below to do it on your own dataset.

Steps:

First, select Cell G5.
After that, insert the following formula

=COUNTIF($D$5:$D$14,F5)

Here, in the COUNTIF function, we selected Cell D5:D14 as the range and selected Cell F5 as criteria. Here, it will count all the Yes votes.

Now, press ENTER.
Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Finally, you will get the total count of votes of different criteria.

2. Use of Combined Formula to Tally Votes in Excel

Now, we will use the SUM function, FREQUENCY function, IF function, MATCH function and ROW function to tally votes in Excel. Go through the steps to do it on your own.

Steps:

In the beginning, select cell G5.
Then, insert the following formula

=SUM(FREQUENCY(IF($D$5:$D$14=F5,MATCH($D$5:$D$14,$D$5:$D$14,0)),ROW($D$5:$D$14)-ROW($D$5)+1))

Formula Breakdown

ROW($D$5)—–> The ROW function returns the row number.
- Output: {5}
ROW($D$5:$D$14)-ROW($D$5)+1)—–> turns into
- ROW{“Yes”;”No”;”Yes”;”Not Sure”;”Yes”;”No”;”Yes”;”Yes”;”No”;”Not Sure”}-{5}+1)—–>
  - Output: {1;2;3;4;5;6;7;8;9;10}
MATCH($D$5:$D$14, $D$5:$D$14,0))—–> The MATCH function returns the relative position of the items in the range.
- Output: {1;2;1;4;1;2;1;1;2;4}
IF($D$5:$D$14=F5,MATCH($D$5:$D$14, $D$5:$D$14,0))—–>The IF function returns 1 value for a TRUE result, and FALSE for a FALSE result.
- IF($D$5:$D$14=”Yes”,{1;2;3;4;5;6;7;8;9;10})—–> turns into
  - Output: {1;FALSE;1;FALSE;1;FALSE;1;1;FALSE;FALSE}

FREQUENCY(IF($D$5:$D$14=F5,MATCH($D$5:$D$14, $D$5:$D$14,0)),ROW($D$5:$D$14)-ROW($D$5)+1))—–> the FREQUENCY function returns how often values occur within a set of data.
- FREQUENCY({1;FALSE;1;FALSE;1;FALSE;1;1;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10})—–> becomes
  - Output: {5;0;0;0;0;0;0;0;0;0;0}
SUM(FREQUENCY(IF($D$5:$D$14=F5,MATCH($D$5:$D$14, $D$5:$D$14,0)),ROW($D$5:$D$14)-ROW($D$5)+1))—–> The SUM function returns the sum of values supplied.
- SUM({5;0;0;0;0;0;0;0;0;0;0})—–> turns into
  - Output: 5
  - Explanation: It will tally all the Yes votes.

After that, press ENTER.
Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Finally, you will get the tally of the votes.

Read More: How to Make a Tally Sheet in Excel

3. Applying SUMIF Function to Tally Votes

In the third method, we will apply the SUMIF function to tally votes. Here, we have the following dataset containing the Name, Age, Count, and Vote of some citizens.

Steps:

First, select Cell H5.
Then, insert the following formula

=SUMIF($E$5:$E$14,G5,$D$5:$D$14)

Here, in the SUMIF function, we selected Cell E5:E14 as range, Cell G5 as criteria and Cell D5:D14 as sum_range. Here, it will tally all the Yes votes.

Now, press ENTER.
Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Finally, you will get the tally of the votes.

Read More: How to Tally a Column in Excel

4. Creating Voting System to Tally Votes

In the final method, we will show you how to create a voting system to tally votes. Here, we have a dataset containing different Candidates and their number of votes. Now, we will create a voting system for this dataset.

Steps:

First, open the Developer tab >> go to Insert >> from Form Controls choose Spin Button.

Now, Spin Button will appear on the worksheet.
After that, select the Spin Button and Right Click.
Then, select Format Control.

Now, the Format Control box will appear.
Then, select Cell C4 as Cell link.
After that, press OK.

Now, the Spin Button is connected to Cell C4.
To check this, press the Upward Button.
Now, notice that the vote has increased from 11 to 12.

After that, insert 3 more Spin Buttons for Cell C5:C7 using the same way(anchor).

Then, select the Cell B4:C7.
After that, open the Insert tab >> from the Chart section >> click on Bar Charts.

Then, from Bar Charts >> select 2-D Column.

Now, a Bar Chart will appear.

Then, change the Chart Title as Votes.

After that, you can add Data Labels.

Finally, you will get the tally of votes by creating a voting system.

Now, you can change the no of votes anytime by using the Spin Button. Here, when we clicked on the Upward Button for Candidate 1, the vote increased from 12 to 13.

If we clicked on the Downward Button for Candidate 2, the vote decreased from 31 to 30.

Things to Remember

Here, you cannot input more than one criterion in the COUNTIF function.
You may find the VALUE Error in case of using the SUMIF function if you use it in case of strings longer than 255 characters.
The FREQUENCY function will show the #NAME error if you misspell the function name.

Practice Section

In the Excel file, you will get a dataset like an image given below in this article to practice the explained methods on your own.

Download Practice Workbook

Tally Votes.xlsx

Conclusion

So, in this article, you will find 4 ways to tally votes in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. Thank you!

<< Go Back to Tally in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Tally Votes in Excel (4 Suitable Methods)

How to Tally Votes in Excel: 4 Suitable Methods

1. Using COUNTIF Function to Tally Votes in Excel

2. Use of Combined Formula to Tally Votes in Excel

3. Applying SUMIF Function to Tally Votes

4. Creating Voting System to Tally Votes

Things to Remember

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply