Sum Marks for Section B
I need to sum the marks where 'Question Type' is 'Essay' and 'Grader' is 'Mr. Smith'.
=SUMIFS(D2:D8, B2:B8, "Essay", C2:C8, "Mr. Smith") How it works: The SUMIFS function is designed to sum cells that meet multiple criteria. In this formula, `D2:D8` is the range of marks to be summed. `B2:B8` is the first range to check for criteria, and `"Essay"` is the specific criterion for 'Question Type'. `C2:C8` is the second range to check, and `"Mr. Smith"` is the specific criterion for 'Grader'. The function will only sum a mark if both the 'Question Type' in the corresponding row is 'Essay' AND the 'Grader' in that same row is 'Mr. Smith'.
Data Setup
| Student ID | Question Type | Grader | Marks |
|---|---|---|---|
| S001 | Multiple Choice | Ms. Jones | 15 |
| S002 | Essay | Mr. Smith | 25 |
| S003 | Short Answer | Ms. Jones | 10 |
| S004 | Essay | Mr. Smith | 30 |
| S005 | Multiple Choice | Mr. Smith | 12 |
| S006 | Essay | Ms. Jones | 20 |
| S007 | Essay | Mr. Smith | 18 |
| S008 | Short Answer | Mr. Smith | 8 |
Step-by-Step Guide
Open your Excel worksheet containing the student marks data.
Identify the column containing the 'Marks' you want to sum (e.g., Column D). This will be your 'sum_range'.
Identify the column containing the 'Question Type' (e.g., Column B). This will be your first 'criteria_range'.
Identify the column containing the 'Grader' (e.g., Column C). This will be your second 'criteria_range'.
In an empty cell, type the SUMIFS function: `=SUMIFS(D2:D8, B2:B8, "Essay", C2:C8, "Mr. Smith")`
Press Enter to get the total sum of marks that meet both conditions.