SUMIFS function

What you will build

Sum Amount for rows that are both North and Paid and return 200.

Learning objective

Enter the formula in a blank output area, understand every argument, compare the result with the visible source data and diagnose range or criterion mistakes.

Open the practice workbook

Use the sample below and keep the full source table visible while checking the result.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

The _result workbook shows the complete clean source table before entering the SUMIFS formula.

Open the sample workbook in Google Drive (access required) ↗

Hands-on practice

Step 1: Choose a blank summary cell such as H2 and enter =SUMIFS(E2:E6,B2:B6,"North",F2:F6,"Paid").

=SUMIFS(E2:E6,B2:B6,"North",F2:F6,"Paid")

Cell H2 is selected, the formula bar shows the real SUMIFS formula, and the result is 200 with the complete source table visible.

How this formula works=SUMIFS(E2:E6,B2:B6,"North",F2:F6,"Paid")

Overall meaning of the whole formula
- Add values from E2:E6 only for rows where Region is "North" and Status is "Paid" at the same time.
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- `sum_range` is required; it accepts the numeric range to add. In this lesson the argument passed is `E2:E6`.
- `criteria_range1` is required; it accepts a range whose cells are tested. In this lesson the argument passed is `B2:B6`.
- `criterion1` is required; it accepts a value, cell reference, or condition text matched against the preceding criteria range. In this lesson the argument passed is `"North"`.
- `criteria_range2` is optional; it accepts a range whose cells are tested. In this lesson the argument passed is `F2:F6`.
- `criterion2` is optional; it accepts a value, cell reference, or condition text matched against the preceding criteria range. In this lesson the argument passed is `"Paid"`.
Whole-expression evaluation
- SUMIFS starts with the sum range, then criteria-range/criterion pairs.
- E2:E6 is the Amount range.
- B2:B6,"North" and F2:F6,"Paid" must both match.
- The matching amounts are 120 and 80, totaling 200.
Why the displayed final result appears
- The matching amounts are 120 and 80, totaling 200.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

After testing E2 from 120 to 130 and restoring it, H2 returns to 200 while the SUMIFS formula remains visible.

Step 2: Compare the returned result with the visible source rows before using it in a report.

Checkpoint

Formula	Verified result
=SUMIFS(E2:E6,B2:B6,"North",F2:F6,"Paid")	200

Verified against the visible sample data.

Common mistakes and fixes

Correction: return to the exact range and command named in the steps, then compare the live sheet with the Formula, Verified result source fields.

Independent practice

Change one relevant input deliberately, predict the new result first, verify it in Sheets, then restore the original sample.

Key takeaways

✓Complete “SUMIFS function” using the visible Formula, Verified result sample so each action and the final state can be checked against the source data.
✓Choose a blank summary cell such as H2 and enter =SUMIFS(E2:E6,B2:B6,"North",F2:F6,"Paid").
✓Compare the returned result with the visible source rows before using it in a report.
✓If the result differs, repeat “Choose a blank summary cell such as H2 and enter =SUMIFS(E2:E6,B2:B6,"North",F2:F6,"Paid").” and then “Compare the returned result with the visible source rows before using it in a report.”. Verify the working table still uses Formula, Verified result before changing source values.
✓Change one relevant input deliberately, predict the new result first, verify it in Sheets, then restore the original sample.

Continue learning

Next lesson: IF function.

What you will build

Sum Amount for rows that are both North and Paid and return 200.

Learning objective

Enter the formula in a blank output area, understand every argument, compare the result with the visible source data and diagnose range or criterion mistakes.

Open the practice workbook

Use the sample below and keep the full source table visible while checking the result.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Open the sample workbook in Google Drive (access required) ↗

Hands-on practice

Step 1: Choose a blank summary cell such as H2 and enter =SUMIFS(E2:E6,B2:B6,"North",F2:F6,"Paid").

=SUMIFS(E2:E6,B2:B6,"North",F2:F6,"Paid")

How this formula works=SUMIFS(E2:E6,B2:B6,"North",F2:F6,"Paid")

Overall meaning of the whole formula
- Add values from E2:E6 only for rows where Region is "North" and Status is "Paid" at the same time.
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- `sum_range` is required; it accepts the numeric range to add. In this lesson the argument passed is `E2:E6`.
- `criteria_range1` is required; it accepts a range whose cells are tested. In this lesson the argument passed is `B2:B6`.
- `criterion1` is required; it accepts a value, cell reference, or condition text matched against the preceding criteria range. In this lesson the argument passed is `"North"`.
- `criteria_range2` is optional; it accepts a range whose cells are tested. In this lesson the argument passed is `F2:F6`.
- `criterion2` is optional; it accepts a value, cell reference, or condition text matched against the preceding criteria range. In this lesson the argument passed is `"Paid"`.
Whole-expression evaluation
- SUMIFS starts with the sum range, then criteria-range/criterion pairs.
- E2:E6 is the Amount range.
- B2:B6,"North" and F2:F6,"Paid" must both match.
- The matching amounts are 120 and 80, totaling 200.
Why the displayed final result appears
- The matching amounts are 120 and 80, totaling 200.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Step 2: Compare the returned result with the visible source rows before using it in a report.

Checkpoint

Formula	Verified result
=SUMIFS(E2:E6,B2:B6,"North",F2:F6,"Paid")	200

Verified against the visible sample data.

Common mistakes and fixes

Correction: return to the exact range and command named in the steps, then compare the live sheet with the Formula, Verified result source fields.

Independent practice

Change one relevant input deliberately, predict the new result first, verify it in Sheets, then restore the original sample.

Key takeaways

✓Complete “SUMIFS function” using the visible Formula, Verified result sample so each action and the final state can be checked against the source data.
✓Choose a blank summary cell such as H2 and enter =SUMIFS(E2:E6,B2:B6,"North",F2:F6,"Paid").
✓Compare the returned result with the visible source rows before using it in a report.
✓If the result differs, repeat “Choose a blank summary cell such as H2 and enter =SUMIFS(E2:E6,B2:B6,"North",F2:F6,"Paid").” and then “Compare the returned result with the visible source rows before using it in a report.”. Verify the working table still uses Formula, Verified result before changing source values.
✓Change one relevant input deliberately, predict the new result first, verify it in Sheets, then restore the original sample.

Continue learning

Next lesson: IF function.

SUMIFS function

What you will build

Learning objective

Open the practice workbook

Practice in your own copy

Hands-on practice

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

SUMIFS function

What you will build

Learning objective

Open the practice workbook

Practice in your own copy

Hands-on practice

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments