How to Tally Survey Results in Excel (Step by Step)

In the following Patient Satisfaction Survey, you can see some survey questions and their corresponding feedback. All the feedback is recorded against the User IDs. The feedback is categorized as Great, Fair, and Poor. The questions that didn’t get any feedback are left blank. We will use this survey result to show you how to tally them.

Step 1 – Count All Feedback to Tally Survey Results

Case 1.1 – Counting Empty Responses

Insert the following formula in I6 and press Enter to apply it:

=COUNTBLANK(C$5:C$16)

Formula Breakdown

Here, the range C$5:C$16 is the range of the 1st Question column. In the range, only the row numbers are locked. Thus, when you drag the formula to the right, only the column number changes but the rows.

Drag the bottom-right corner of cell I6 until cell L6 to copy the formula.
You will see the total number of empty responses against all the questions.

Case 1.2 – Counting Non-Empty Response

Insert the following formula in cell I7 and press Enter.

=COUNTA(C$5:C$16)

Formula Breakdown

Here, the range C$5:C$16 is the range of the 1st Question column. In the range, only the row numbers are locked. Thus, when you drag the formula to the right, only the column number changes but the rows.

Drag the bottom-right corner of cell I7 to cell L7 to copy the formula.
You will see the total number of non-empty responses against all the questions.

Case 1.3 – Count Total Number of Responses

Copy the following formula in cell I8 and hit Enter.

=SUM(I$6:I$7)

Formula Breakdown

Here, the range I$6:I$7 includes the count value of empty and non-empty responses. In the range, only the row numbers are locked. Thus, when you drag the formula to the right, only the column number changes but the rows.

Drag the bottom-right corner of cell I8 to cell L8.
You will see the total number of responses to all the questions.

Step 2 – Calculate Feedback Percentages to Tally Survey Results

Insert the following formula in cell I10. This formula will calculate the percentages of Great, Fair, Poor:

=COUNTIF(C$5:C$16,$H10)/I$7

Formula Breakdown

C$5:C$16: Here, the range C$5:C$16 is the range of the 1st Question In the range only the row numbers are locked. Thus, when you drag the formula to the right, only the column number changes but the rows.
$H10: It refers to feedback, “Great”. Thus, the formula only counts the feedback that is “Great”. Dollar Sign ($) before the column number locks the column to H. I locked only the column because feedback is available only in the range H10:H12.
I$7: It refers to the total non-empty response. Here, I locked only the row number because the number of total non-empty responses is only available in the range I7:L7.

Drag the bottom-right corner of cell I10 up to cell L10.
Select the range I10:L10 and drag the bottom-right corner of the selection area up to cell L12. This copies the formula in cell I10 all over the range I10:L12.
With the range I10:L12 selected go to Home and select the Percentage icon.
Click on the Increase Decimal icon two times.

Step 3 – Create a Survey Report

Select the range H4:L12.
Press Ctrl + C.

Go to a blank worksheet.
Select a cell and right-click on it.
From the context menu, click on Transpose in the Paste Options.

Your copied rows will be converted into columns and vice versa like in the following screenshot.

Select the Parameter, No Response, Response, and Total Response columns alongside their headers.
Go to Insert, select Insert Column or Bar Chart, and choose 2-D Clustered Column.

This will generate a Question vs. Response chart. In the chart, Response, No Response, and Total Response data will be represented with 3 different color columns.