# Analyze open-ended survey answers in Excel `=HUNCH.MULTI` reads one open-ended survey answer and returns yes-or-no for up to ten themes at once, one probability column per theme. Point COUNTIF or a PivotTable at the results and a column of free text becomes a table you can report from. Updated 28 September 2026. Source: https://hunchsheet.app/formulas/analyze-survey-responses-excel ## One formula, one column per theme ``` =HUNCH.MULTI(A2:A9, C1:E1) ``` In Google Sheets: `=HUNCHMULTI(A2:A9, C1:E1)` C1:E1 holds three questions, one per theme: "Mentions confusing onboarding or setup?", "Mentions a missing integration?" and "Mentions the price or cost being too high?". Each response is read once and gets a probability in every theme column, because one open-ended answer often touches more than one theme at a time, which is exactly how people write them. ## Choosing the themes Read 30 or 40 responses before writing a single formula. Write down every distinct reason you see, merge close ones, and stop at six or seven themes; a MULTI call with ten near-identical questions is harder to read than one with six clear ones. Run it, then read a sample of the responses that scored low on every theme. That is your signal to add one more theme or accept that some answers are too short to code. ## Real outputs Eight answers to "what is the one thing we should improve", with the model's answers as returned on 28 September 2026. | Response | Confusing onboarding? | Missing integration? | Too expensive? | | --- | --- | --- | --- | | The onboarding checklist stops at step 3 and never says what is next. | 0.93 | 0.19 | 0.01 | | We need a working Salesforce integration, right now it is a manual CSV upload every week. | 0.08 | 0.97 | 0.03 | | Everything is great, just wish the annual plan was not twice the monthly one added up. | 0.04 | 0.11 | 0.80 | | Honestly the first week was confusing, I did not know where to find my team's settings. | 0.98 | 0.04 | 0.01 | | More than happy with the price, my only gripe is no Slack integration. | 0.02 | 0.99 | 0.03 | | It works fine once you are set up, but that first week nearly made us quit. | 0.92 | 0.05 | 0.03 | | Nothing, I would just add more example templates for new users. | 0.22 | 0.04 | 0.01 | | The tool is good but training my team took far longer than it should have, and the license renewal jumped 40 percent this year. | 0.64 | 0.03 | 0.95 | Row 5 is the cleanest read on how MULTI handles a negation: the response opens with "happy with the price," and the too-expensive column still comes back at 0.03, not because the model spotted the word price but because it read past it to what the sentence says. Row 8 is the opposite case, one answer hitting two themes at once: 0.64 on confusing onboarding for the training complaint, and 0.95 on too expensive for the renewal jump, in a single response that a one-pick classifier would have had to squeeze into a single theme. Row 7, "nothing, I would just add more example templates," stays under 0.25 on all three themes, which is correct: the answer is real feedback, not about any of the three themes on this list yet. ## Count and pivot the themes ``` =COUNTIF(C2:C9, ">=0.5") ``` Repeat across each theme column for a one-line summary, or build a PivotTable with the three theme columns as values and Count as the summary. Because a response can score above 0.5 in two columns at once, the totals will not add up to the number of responses, and that is correct: it reflects overlap, not an error. > Tip: A helper column with `=SUM(C2:E2)` flags responses that hit no theme at all when the total stays near zero. Read those; they are either a theme you have not added yet or an answer with nothing usable in it. ## Limits - HUNCH.MULTI takes up to ten yes/no questions in one call, spilling one column each. - One credit per question per response. Four themes over 500 responses is 2,000 credits, about $11.60 on the Starter pack. - A one-sentence answer gives the model less to work with than a paragraph, and the probabilities sit closer to the middle. That is the honest answer, not a broken one. - Cells calculating together travel in requests of up to 40 rows, and answers are cached for six hours inside Excel. ## Setup in three steps 1. Sideload the add-in from [hunchsheet.app/excel](https://hunchsheet.app/excel). 2. Save your key once: `=HUNCH.SETKEY("hunch_...")`, then delete the cell. 3. Write your theme questions across a header row and point HUNCH.MULTI at the response column and that row. ## See also - [HUNCH.MULTI reference](https://hunchsheet.app/docs/hunchmulti) for the full argument list and the ten-question limit. - [The same job in Google Sheets](https://hunchsheet.app/formulas/code-open-ended-survey-responses). - [Categorize support tickets in Excel](https://hunchsheet.app/formulas/categorize-support-tickets-excel), another HUNCH.MULTI worked example. ## Questions ### How many themes can I code at once? Up to ten questions in one HUNCH.MULTI call. Past that, split into a second formula with the remaining themes. ### What if a response does not fit any of my themes? Every theme column stays low. A helper SUM column flags those rows; read a handful, and either add a theme for the pattern you find or accept that some answers are noise. ### Is this different from one HUNCH.PICK column for a primary theme? Yes. A pick column forces one theme per response and pivots cleanly, but hides the responses that raise two problems at once. Some teams run both: MULTI for full coverage, PICK for the single headline theme. See [the HUNCH.PICK reference](https://hunchsheet.app/docs/hunchpick). ### Does the wording of the question matter? Yes. "Mentions price" and "is upset about price" are different questions with different answers. Keep the wording identical across survey waves so the theme columns stay comparable over time. ### Can I run this on translated responses? You can, but accuracy is highest in English. If a meaningful share of responses are in another language, test 20 of them against your own reading before trusting the column.