Calculate an Excel Score From Yes, No, and N/A Answers
Question details
Calculate a 1–3 score for each question from its Yes, No, and N/A statement responses, even when questions contain different numbers of statements.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The worksheet contains multiple statement responses for each question. A score of 1 is required for 50% or fewer Yes responses, 2 for more than 50% but less than 100%, and 3 for 100% Yes responses.
- Observed behavior
- Questions have variable-length response groups, so each score must use only the responses belonging to its question. N/A responses should not reduce the Yes percentage when they are excluded from the valid-response count.
Put each statement on its own row and use consistent response text such as Yes, No, and N/A. Decide whether N/A should be excluded from scoring; the formulas below exclude it from the denominator.
Score each question with helper columns for valid and Yes responses
Use this method when each statement is stored on a separate row and question names or IDs can be repeated for every related statement.
Assume column A contains the question ID or question name and column B contains the response. Repeating the question ID on every statement row lets the formula count variable-length question groups without using merged cells.
N/A is excluded by counting only Yes and No as valid responses. A question containing only N/A responses returns a blank score.
In column A, enter the same question ID or name beside every statement that belongs to that question. If the source uses merged question cells, unmerge them and use Home > Fill > Down to fill the question label into the blank rows.
In C2, enter =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"Yes")+COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"No") and fill the formula down. This counts only responses that should affect the score.
In D2, enter =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,"Yes") and fill down to count Yes responses for the current question.
In E2, enter =IF(C2=0,"",IF(D2=C2,3,IF(D2/C2>0.5,2,1))) and fill down. The result is 3 for all Yes, 2 for more than 50% Yes, and 1 for 50% or fewer Yes responses.
If you only want the score on the first row of each question, replace the formula in E2 with =IF(A2=A1,"",IF(C2=0,"",IF(D2=C2,3,IF(D2/C2>0.5,2,1)))) and fill down.
Use a reusable LAMBDA formula for a single response range
Use this option in Excel versions that support LET and LAMBDA when you want to apply the same score logic to separate response ranges.
Calculate Yes, No, and N/A Scores in WPS Spreadsheets
WPS Spreadsheets can calculate question-level scores from variable numbers of responses with COUNTIFS and IF formulas. Organize statements in rows, count valid Yes and No responses by question, and automatically return the required 1–3 score while excluding N/A entries.
- 1. Open the response worksheet: Open the workbook in WPS Spreadsheets and place question IDs in one column and Yes, No, or N/A responses in the next column.
- 2. Fill question IDs down: Select blank question-label cells beneath each heading and use Home > Fill > Down so every response row has a question ID.
- 3. Add valid-response and Yes counts: Use COUNTIFS formulas in helper columns to count Yes plus No responses and to count Yes responses for the current question ID.
- 4. Enter the score formula: In a score column, use =IF(C2=0,"",IF(D2=C2,3,IF(D2/C2>0.5,2,1))) after C contains valid responses and D contains Yes responses.
- 5. Fill and review results: Drag the fill handle down the score column and verify that N/A-only questions remain blank and 100% Yes questions return 3.
Frequently Asked Questions
Do N/A answers count toward the percentage of Yes responses?
Not in the recommended formula. It counts only Yes and No as valid responses, so N/A is excluded from both the Yes count and the denominator.
What score is returned when exactly half of the valid responses are Yes?
The score is 1. The formula assigns 2 only when the Yes percentage is greater than 50%, not when it equals 50%.
How do I score a question with all Yes responses and some N/A responses?
It returns 3 as long as every valid response is Yes. For example, two Yes responses and one N/A response have a valid-response total of two and a Yes percentage of 100%.
What happens if a question has only N/A responses?
The formula returns a blank cell because there are no valid Yes or No responses from which to calculate a percentage. You can replace "" in the formula with a custom label such as "N/A" if preferred.
Can I use the formula when questions have different numbers of statements?
Yes. When the question ID is repeated on every statement row, COUNTIFS counts all matching rows for that ID, regardless of how many statements belong to the question.
