Formula Errors

How to Calculate Yes, No, and Sort Of Scores in Excel

Olivia Miller Oct 21, 2026 868 views

Question details

The user needs an Excel formula to calculate a percentage score from a list of requirements where 'Yes' equals 1 point, 'No' equals 0 points, and 'Sort Of' equals 0.5 points. The calculation must count unanswered rows in the total denominator but exclude section headings.

How to Calculate Yes, No, and Sort Of Scores Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Scoring a questionnaire or requirements list where text-based responses need to be translated into weighted numerical values to calculate a final completion percentage.
Observed behavior: Using the COUNTA function only counts completed cells, which causes the denominator to shrink and incorrectly inflates the score to 100% even when several requirements are left unanswered.

Before you start

Ensure your requirements list is structured clearly, with one column containing the requirement descriptions (and section headers) and another column dedicated exclusively to the 'Yes', 'No', or 'Sort Of' responses.

Solution 1Recommended

Use COUNTIFS to Dynamically Exclude Section Headings

This method calculates the exact total of valid requirements dynamically, ensuring unanswered questions are counted while section headers are completely ignored.

To get an accurate percentage, your denominator needs to count all valid rows. Because COUNTA ignores blank responses, we use COUNTIFS to look at the requirement description column instead, counting it as long as it isn't empty and doesn't contain the word 'Section'.

Identify your data ranges

Locate the column containing your requirement descriptions (e.g., Column A) and the column where users input their responses (e.g., Column C).

Enter the scoring numerator

Select the cell where you want the final score to appear. Begin the formula by calculating the points: =(COUNTIF(C6:C87,"Yes")+COUNTIF(C6:C87,"Sort Of")/2).

Add the dynamic denominator

Divide the numerator by the total number of valid rows using COUNTIFS. Add /COUNTIFS($A$6:$A$87,"<>Section*",$A$6:$A$87,"<>") to the end of your formula.

Format as a percentage

Press Enter to calculate the decimal result. Navigate to the Home tab and click the Percentage Style icon (%) to display the result as a proper percentage.

Use COUNTIFS to Dynamically Exclude Section Headings

Formula Breakdown: The full formula =(COUNTIF(C6:C87,"Yes")+COUNTIF(C6:C87,"Sort Of")/2)/COUNTIFS($A$6:$A$87,"<>Section*",$A$6:$A$87,"<>") accurately assigns 1 point for Yes, 0.5 for Sort Of, and divides by the total number of actual questions.

Solution 2

Use a Fixed Denominator for a Static Requirements List

If your form never changes and always has a set number of requirements, you can hardcode the total denominator to simplify the formula.

Advanced Spreadsheet Tool

Calculate Complex Survey Scores Easily in WPS Spreadsheet

WPS Office provides a highly compatible, free spreadsheet application equipped with all standard mathematical and statistical functions. You can flawlessly execute complex scoring formulas like COUNTIF and COUNTIFS without worrying about compatibility issues.

1. Open your questionnaire data: Launch WPS Spreadsheet and open the workbook containing your requirements list.
2. Select the result cell: Click on the cell where you want the final percentage score to be calculated.
3. Input the scoring formula: Type the exact COUNTIF and COUNTIFS formula into the formula bar to evaluate the responses.
4. Format the output: Press Enter to compute the result, then click the Percentage (%) icon on the Home tab to format it correctly.

100% compatible with Microsoft Excel formulas, meaning COUNTIF and COUNTIFS work identically.Intuitive formula hints and syntax highlighting to help you avoid errors.Free to use for everyday data analysis, survey scoring, and reporting.Lightweight software that processes large datasets and complex logic quickly.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't COUNTA work for calculating my total denominator?

The COUNTA function only counts cells that are not empty. If a user leaves a response cell blank, COUNTA ignores it entirely. This artificially reduces your total question count (the denominator), which inflates the final percentage incorrectly.

How do I assign negative points to 'No' responses?

If you want 'No' responses to penalize the score instead of just giving zero points, you can subtract them in the numerator. For example, to subtract 1 point for every 'No', add '-COUNTIF(C6:C87,"No")' inside the numerator parentheses of your formula.

Can I adapt this formula to ignore entirely blank rows at the bottom of my sheet?

Yes. By using COUNTIFS($A$6:$A$87,"<>") in the denominator, the formula checks the requirement description column (Column A). It will only count rows that actually contain text, safely ignoring completely blank rows at the bottom of your designated range.

Excel & Data Problems