With exam grade A = 5 points and F = 0 points, what is the formula for John's score (16 points)?
{=SUM (SUMIF (A5:A11,B2:F2,B5:B11))}
- This is an array formula (enter the formula and press Ctrl+Shift+Enter).
SUMIF(A5:A11,B2:F2,B5:B11)
- This formula requires Excel to search A5:A11 one by one for matches to the contents of B2:F2.
- If it matches, convert the content to the corresponding number in B5:B11.
- For example, Excel first looks in A5:A11 for which cell equals B2 (i.e., 'F').
- Excel finds that the 5th cell in A5:A11 (i.e., A10) is 'F'.
- It then converts to the 5th cell in B5:B11 (i.e., B10), which is '0'.
- Similarly, looking up B2:F2 one by one yields the five numbers 0, 5, 3, 5, 3.
SUM(SUMIF(A5:A11,B2:F2,B5:B11))
- Finally SUM adds the five numbers {0,5,3,5,3} to calculate the total.
