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.

Download Practice Workbook

Tally Votes.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!