Function Problems

How to Compare Survey Responses and Calculate Similarity in Excel

Steven ParkerSteven Parker Nov 3, 2026 870 views

Question details

The user needs a method to compare survey answers between individuals to determine their response similarity as a percentage.

How to Compare Survey Responses and Calculate Similarity in Excel
Product
Excel
Device & OS
not provided
Scenario
Analyzing survey results collected via Microsoft Forms or similar tools where multiple respondents' answers need to be compared.
Observed behavior
The user is looking for an automated formula-driven approach in Excel to output a match percentage (e.g., 89%) between two respondents.
Before you start

Ensure your survey data is organized in a tabular format where each row represents a single respondent and each column represents a survey question. Export your data from Microsoft Forms to Excel before applying formulas.

Solution 1Recommended

Use SUMPRODUCT and COUNTA Formulas to Calculate Match Percentage

This method uses a combination of formulas to compare two rows of survey answers and calculate the exact percentage of matching responses.

The SUMPRODUCT function can evaluate multiple arrays to see where answers match exactly. By dividing this count by the total number of questions answered (using COUNTA), you get an accurate similarity rate.

1
Organize your data

Make sure Respondent A's answers are in Row 2 (e.g., B2:Z2) and Respondent B's answers are in Row 3 (e.g., B3:Z3).

2
Enter the formula

In an empty cell where you want the similarity percentage to appear, type `=SUMPRODUCT(--(B2:Z2=B3:Z3))/COUNTA(B2:Z2)` and press Enter.

3
Format as a percentage

Select the cell containing the result, go to the Home tab on the ribbon, and click the '%' (Percentage Style) icon to convert the decimal into a readable percentage.

Use SUMPRODUCT and COUNTA Formulas to Calculate Match Percentage
Formula Breakdown: The double unary operator (--) converts the TRUE/FALSE results of the array comparison into 1s and 0s, allowing SUMPRODUCT to count the exact matches.
Advanced Data Analysis

Calculate Survey Similarity Seamlessly in WPS Spreadsheet

WPS Office offers a powerful and free Spreadsheet tool that fully supports advanced array functions like SUMPRODUCT and COUNTA, making survey analysis fast, accurate, and visually appealing.

  1. 1. Open your survey data: Launch WPS Spreadsheet and open your .xlsx file containing the exported survey responses.
  2. 2. Apply the comparison formula: Select a blank cell and input `=SUMPRODUCT(--(B2:Z2=B3:Z3))/COUNTA(B2:Z2)` to compare two rows.
  3. 3. Format the result: Click the Percentage Style button on the Home ribbon to convert the calculation into a match percentage.
Fully compatible with Microsoft Excel formulas and .xlsx filesLightweight and fast, even when analyzing thousands of survey responsesBuilt-in data visualization tools to chart your similarity results easily
microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMPRODUCT formula returning a #VALUE! error?

This error occurs when the ranges you are comparing are not the exactly same size. Ensure that both arrays in the formula (e.g., B2:Z2 and B3:Z3) cover the exact same number of columns.

How do I ignore blank answers in the similarity calculation?

To exclude unanswered questions from being counted as false matches, you can modify the formula to explicitly ignore blanks by nesting an IF condition or adding an additional array criteria like `*(B2:Z2<>"")` inside the SUMPRODUCT function.

Does this formula work for text-based survey answers?

Yes, the exact match formula `(B2:Z2=B3:Z3)` evaluates both numerical ratings and text-based responses. However, text must be spelled identically; extra spaces or typos will result in a non-match.

Can I conditionally format the matching answers?

Yes. Select Respondent B's row, go to Home > Conditional Formatting > New Rule, choose 'Use a formula to determine which cells to format', enter `=B3=B2`, and choose a highlight color to visually spot the matching answers.