The Problem
Survey responses often mean the same thing while still being typed in different ways. One person enters y, another enters Yes, and another types YES. If the list stays that way, the summary becomes harder than it should be.
This challenge normalizes those variations into a single official output. Once every row is converted to YES or NO, counting the final result becomes simple.
How We Solve It
The most direct shortcut is to look only at the first letter of the response. If that first letter is a Y after converting it to uppercase, the result should be YES. Otherwise it becomes NO.
Method 1: IF with UPPER and LEFT
Method 1: Normalize the response by checking only the first letter.
This is the method the solution uses. LEFT takes the first character, UPPER removes casing differences, and IF turns the result into the standard label.
This solves the challenge because the worksheet only needs to distinguish yes-type answers from no-type answers. By reducing each response to its first letter, the formula can normalize several messy variations into one clean YES or NO result.
=IF(UPPER(LEFT(B2, 1)) = "Y", "YES", "NO")
Method 2: SWITCH for explicit mapping
Method 2: Map known variants explicitly when the response list is small.
SWITCH works well when you want to list known variants one by one. It is not the required validator pattern here, but it is a useful option when the accepted inputs are limited and clearly defined.
This solves the same normalization problem in a more explicit way. Instead of relying on the first letter, it matches each known response variation to the official output you want the sheet to use.
=SWITCH(UPPER(B2), "Y", "YES", "YES", "YES", "N", "NO", "NO", "NO")
Method 3: Use a lookup table
Method 3: Use a mapping table when the raw response list may expand.
If future data could include more variants, a lookup table makes the system easier to extend. The normalization logic stays outside the main formula, which can be helpful when a workbook has many accepted inputs.
This solves the long-term maintenance problem. When new raw response styles appear, the team can update the mapping table instead of rewriting the main formula in every row.
=VLOOKUP(UPPER(B2), $F$2:$G$10, 2, FALSE)
Function Explanation
1. UPPER
UPPER converts text to uppercase. That removes case differences so values like y and Y can be treated the same way.
Learn more this functionUPPER
2. LEFT
LEFT returns characters from the start of a string. In this worksheet it lets us focus on the first letter, which is enough for the simple yes/no rule.
Learn more this functionLEFT
3. COUNTIF
COUNTIF counts how many rows ended up with the label YES. That makes the summary quick once the normalization is done.
This shortcut assumes the first letter is enough to separate yes from no. If a dataset contains blanks or more complicated answers, the logic usually needs an extra check.
Normalize every survey response to YES or NO, then finish the summary so the worksheet shows the total response count and how many official YES responses are in the list.