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.


Download Practice Workbook


4 Suitable Methods to Tally Votes in Excel

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.

How to Tally Votes in Excel


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.

How to Tally Votes in Excel Using COUNTIF Function

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.

How to Tally Votes in Excel Using COUNTIF Function

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

Read More: How to Make a Tally Chart in Excel (3 Easy Methods)


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))

Use of Combined Formula to Tally Votes in Excel


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.

Use of Combined Formula to Tally Votes in Excel

Read More: How to Make a Tally Sheet in Excel (3 Quick Methods)


Similar Readings


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.

Applying SUMIF Function to Tally Votes

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.

Applying SUMIF Function to Tally Votes

  • Finally, you will get the tally of the votes.

Read More: How to Tally a Column in Excel (with Quick Steps)


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.

Creating Voting System to Tally Votes

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.

Creating Voting System to Tally Votes

  • 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.

Creating Voting System to Tally Votes

  • 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.

Creating Voting System to Tally Votes

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

  • Now, a Bar Chart will appear.

Creating Voting System to Tally Votes

  • Then, change the Chart Title as Votes.

Creating Voting System to Tally Votes

  • After that, you can add Data Labels.

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

Creating Voting System to Tally Votes

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.

Creating Voting System to Tally Votes

Read More: How to Create a Tally Button in Excel (2 Suitable Examples)


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.

Practice Section


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. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin

Arin

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo