# How to Tally Votes in Excel: 4 Suitable Methods

### Method 1 – Using COUNTIF Function to Tally Votes in Excel

Steps:

• Select Cell G5.
• Insert the following formula

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

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.

• Press ENTER.
• Drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Get the total count of votes of different criteria.

### Method 2 – Use of Combined Formula to Tally Votes in Excel

Steps:

• Select cell G5.
• 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.

• Press ENTER.
• Drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Get the tally of the votes.

### Method 3 – Applying SUMIF Function to Tally Votes

Steps:

• Select Cell H5.
• Insert the following formula
`=SUMIF(\$E\$5:\$E\$14,G5,\$D\$5:\$D\$14)`

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.

• Press ENTER.
• Drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Get the tally of the votes.

### Method 4 – Creating Voting System to Tally Votes

Steps:

• Open the Developer tab >> go to Insert >> from Form Controls choose Spin Button.

• Spin Button will appear on the worksheet.
• Select the Spin Button and Right Click.
• Select Format Control.

• The Format Control box will appear.
• Select Cell C4 as Cell link.
• Press OK.

• The Spin Button is connected to Cell C4.
• Press the Upward Button.
• The vote has increased from 11 to 12.

• Insert 3 more Spin Buttons for Cell C5:C7 using the same way(anchor).

• Select the Cell B4:C7.
• Open the Insert tab >> from the Chart section >> click Bar Charts.

• Fom Bar Charts >> select 2-D Column.

• A Bar Chart will appear.

• Change the Chart Title to Votes.

• Add Data Labels.

Get the tally of votes by creating a voting system.

Change the no of votes anytime by using the Spin Button. We clicked on the Upward Button for Candidate 1, the vote increased from 12 to 13.

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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF