Function Problems

Calculate Points from Multiple Answers with an Excel Formula

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 870 views

Question details

Create one formula that scores several responses based on exact matches, partial matches, and blank cells.

Calculate Points from Multiple Answers with an Excel Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
Responses are in B4:D4, answer keys are in I3:I5, and exact-match point values are in J3:J5.
Observed behavior
The desired formula should add the points for each response while awarding zero for blanks.
Before you start

Enter the three answer keys in I3:I5 and their exact-match point values in J3:J5. Place the responses to score in B4:D4, and choose an empty cell for the total.

Solution 1Recommended

Score each response with one Excel formula

Use this formula to award the assigned points for an exact match, one point for a partial match, and zero for a blank or unmatched response.

B4 is compared with I3, C4 with I4, and D4 with I5 for exact matches. If an answer is not an exact match, the formula checks whether its text appears anywhere in I3:I5 and awards at most one partial-match point.

The comparisons are not case-sensitive. SEARCH treats asterisks and question marks in responses as wildcard characters.

1
Select the result cell

Click an empty cell outside B4:D4, I3:I5, and J3:J5, such as E4.

2
Enter the scoring formula

Paste this formula into the selected cell and press Enter: =SUM(IF(B4="",0,IF(B4=$I$3,$J$3,IF(SUMPRODUCT(--ISNUMBER(SEARCH(B4,$I$3:$I$5)))>0,1,0))),IF(C4="",0,IF(C4=$I$4,$J$4,IF(SUMPRODUCT(--ISNUMBER(SEARCH(C4,$I$3:$I$5)))>0,1,0))),IF(D4="",0,IF(D4=$I$5,$J$5,IF(SUMPRODUCT(--ISNUMBER(SEARCH(D4,$I$3:$I$5)))>0,1,0))))

3
Check and copy the result

Test an exact answer, a partial answer, and a blank in B4:D4. To score additional rows, drag the result cell's fill handle down; the answer-key and point references stay fixed.

Score each response with one Excel formula
Partial-match direction: The formula looks for the response text within any answer key. A response that contains extra text around a key is not necessarily a partial match.
WPS Spreadsheets

Score Multiple Answers in WPS Spreadsheets

Use the same IF, SEARCH, and SUMPRODUCT formula in WPS Spreadsheets to total exact-match and partial-match points while ignoring blank responses.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and select Open to choose your workbook.
  2. 2. Set up the scoring cells: Enter responses in B4:D4, answer keys in I3:I5, and exact-match points in J3:J5.
  3. 3. Add the formula: Select E4, paste the scoring formula from the solution above into the formula bar, and press Enter.
  4. 4. Test and save: Check the total using exact, partial, and blank responses, then select File > Save to save the workbook.
Score multiple responses in one formulaKeep answer keys and point values fixed when copying the formulaOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet functions and controls
microsoft office alternative - wps office

Frequently Asked Questions

How many points does a partial answer receive?

The formula awards one point if the response text appears in any cell in I3:I5. It awards only one partial-match point per response, even if multiple keys contain that text.

Why are blank responses worth zero?

Each response starts with an IF check for an empty cell, such as IF(B4="",0,...). This prevents SEARCH from treating an empty response as a match.

Can I change the partial-match point value?

Yes. In each response's partial-match IF expression, replace the 1 in IF(SUMPRODUCT(...)>0,1,0) with your preferred point value.

Can I use the formula for more rows?

Yes. Drag the result cell's fill handle down. The response references change by row, while the dollar signs keep I3:I5 and J3:J5 fixed.