Formula Errors

Fix Excel Formula Error When Scoring Multiple Microsoft Forms Answers

Huda Qurayshi Oct 1, 2026 868 views

Question details

The user needs to correctly calculate scores for multiple-choice survey responses imported from Microsoft Forms without triggering formula errors.

Fix Excel Formula Error When Scoring Multiple Microsoft Forms Answers

Product: Microsoft Excel
Device & OS: not provided
Scenario: Scoring multiple-choice responses that are exported from Microsoft Forms to Excel via Power Automate, where multiple selected options are stored as a single comma-separated string.
Observed behavior: The scoring formula works successfully for single answers but returns a "too many arguments" error when evaluating cells containing multiple comma-separated answers.

Before you start

Prepare a small sample of your imported Microsoft Forms data containing both single and multiple responses, and clearly define the expected correct answers and scores for testing purposes.

Solution 1Recommended

Use TEXTSPLIT and Array Formulas to Calculate Scores

Break down the comma-separated text into individual array items to compare against your correct answers, avoiding standard IF function limitations.

The "too many arguments" error typically occurs because basic logical functions like IF cannot natively process an unexpected number of comma-separated values as separate arguments. By utilizing dynamic array functions, you can split the text string, evaluate each selected option, and sum the resulting scores.

Identify data cells

Locate the cell containing the user's comma-separated answer (e.g., A2) and the cell containing the correct answer key (e.g., B2).

Apply the TEXTSPLIT function

Use the TEXTSPLIT function to separate the respondent's answers into an array. Type =TRIM(TEXTSPLIT(A2, ",")) to divide the text at each comma and remove any leading or trailing spaces.

Compare with correct answers

Match the user's split answers against the correct answers. You can compare arrays directly using logic like TRIM(TEXTSPLIT(A2, ",")) = TRIM(TEXTSPLIT(B2, ",")).

Sum the final points

Wrap the comparison in a SUM or SUMPRODUCT function to count the total correct matches. A sample formula would be: =SUM(--(TRIM(TEXTSPLIT(A2,","))=TRIM(TEXTSPLIT(B2,",")))). Press Enter to calculate the score.

Use TEXTSPLIT and Array Formulas to Calculate Scores

Dynamic Arrays: Modern spreadsheet software natively supports dynamic arrays, meaning these formulas will automatically process multiple values without requiring legacy Ctrl+Shift+Enter keystrokes.

Solution 2

Pre-Process the Data Using Power Automate

Handle the comma-separated values directly inside the Power Automate flow before the data is ever inserted into Excel.

Advanced Data Processing

Score Complex Survey Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, text splitting, and conditional scoring, making it incredibly easy to process and analyze comma-separated survey data imported from Microsoft Forms.

1. Open your survey dataset: Launch WPS Spreadsheet and open the .xlsx file containing your imported Microsoft Forms responses.
2. Set up a scoring column: Select a blank column next to your response data to calculate the scores.
3. Input the array formula: Enter your dynamic array formula using functions like TEXTSPLIT or FIND to parse the comma-separated text and identify correct answers.
4. Apply to all responses: Drag the fill handle down from the bottom-right corner of the cell to apply the scoring logic to all survey respondents instantly.

Seamlessly supports complex array formulas and SUMPRODUCT logic.Highly compatible with Microsoft Excel (.xlsx) formats and standard functions.Lightweight software that handles large survey datasets without lagging or crashing.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my nested IF formula return a 'too many arguments' error?

The IF function only accepts exactly three arguments: the logical test, the value if true, and the value if false. When a cell contains multiple answers separated by commas, basic extraction formulas might mistakenly feed those extra commas into the IF function, breaking its required structure.

Can I score partial matches in Microsoft Forms responses?

Yes. By combining functions like ISNUMBER and SEARCH within a SUMPRODUCT formula, you can check if a specific correct substring exists within the respondent's overall answer, allowing you to award points even if extra text is present.

Do text splitting formulas work in older spreadsheet versions?

The TEXTSPLIT function is a newer addition to spreadsheet software. If you are using an older version, you must rely on complex combinations of MID, FIND, REPT, and LEN functions to split text, or simply upgrade to a modern, fully-featured alternative like WPS Office.

Excel & Data Problems