How to Compare Survey Responses and Calculate Similarity in Excel
Question details
The user needs a method to compare survey answers between individuals to determine their response similarity as a percentage.

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

Compare One Respondent Against Multiple Others
Use absolute references to lock the primary respondent's data so you can quickly compare their answers against an entire list of people.
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. Open your survey data: Launch WPS Spreadsheet and open your .xlsx file containing the exported survey responses.
- 2. Apply the comparison formula: Select a blank cell and input `=SUMPRODUCT(--(B2:Z2=B3:Z3))/COUNTA(B2:Z2)` to compare two rows.
- 3. Format the result: Click the Percentage Style button on the Home ribbon to convert the calculation into a match percentage.

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.




