# Code open-ended survey responses in Google Sheets

Six hundred free-text answers to "why did you cancel?" do not code themselves. One formula per theme, and one for the primary theme, turn them into a table in an hour.

Updated 22 September 2026. Source: https://hunchsheet.app/formulas/code-open-ended-survey-responses

## One column per theme

```
=HUNCH(E2:E900, "Does this answer give price or cost as a reason?")
```
In Excel: `=HUNCH.ASK(E2:E900, "Does this answer give price or cost as a reason?")`

A yes/no column per theme lets one answer count for several themes, which is how people actually answer. Add columns for the themes your team argues about: missing feature, hard to use, poor support.

## One column for the primary theme

```
=HUNCHPICK(E2:E900, "price|missing feature|hard to use|poor support|switched to a competitor|no longer needed|other")
```
In Excel: `=HUNCH.PICK(E2:E900, "price|missing feature|hard to use|poor support|switched to a competitor|no longer needed|other")`

## Real outputs

| Answer | Price? (0–1) | Primary theme |
| --- | --- | --- |
| Too expensive once the discount ended. | 0.98 | price |
| We switched to a competitor that has the Salesforce integration we needed. | 0.02 | switched to a competitor |
| Nobody on the team used it after the first month. | 0.03 | no longer needed |
| The reports were hard to build and support never got back to me. | 0.05 | poor support |
| We closed the project the tool was bought for. | 0.04 | no longer needed |
| Loved it, but the price doubled at renewal. | 0.92 | price |

"Loved it, but the price doubled" is coded price at 0.92 even though it starts with praise. A keyword search for "expensive" would have missed it.

## The summary table

```
=QUERY(F2:F900, "select F, count(F) where F is not null group by F order by count(F) desc label count(F) 'answers'")
```

> Tip: Spot-check the 0.4 to 0.6 band in each yes/no column. Those are the answers the model finds ambiguous, and they are usually the interesting ones.

## Questions

### How do I pick the themes?

Read 30 answers and write down every reason you see. Merge to six or seven, add other, run the pick column, then read what landed in other. Adjust once.

### Can it handle very short answers?

Yes, but a one-word answer gives the model little to work with, and the probabilities will sit closer to the middle. That is honest, not broken.