SUMIFS & COUNTIFS
Combine conditions without losing track of which rows qualify.
By the end, you’ll be able to:
- Use AND logic across criteria pairs
- Keep every range aligned
- Build and verify an OR calculation without double counting
All conditions apply to the same row
To find East Online revenue, a row must satisfy two tests: Region is East and Channel is Online. S101 and S105 qualify, contributing 120 + 60 = 180. S103 is East but Store, so it is excluded.
SUMIFS and COUNTIFS perform this row-by-row intersection. Adding more criteria makes a narrower requirement; it does not mean “any of these conditions.”
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Region | Channel | Units | Revenue |
| 2 | S101 | East | Online | 4 | 120 |
| 3 | S102 | West | Store | 2 | 80 |
| 4 | S103 | East | Store | 5 | 200 |
| 5 | S104 | West | Online | 3 | 150 |
| 6 | S105 | East | Online | 2 | 60 |
| 7 | S106 | North | Store | 1 | 40 |
The sum range moves to the front
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)sum_range holds numbers to total. Each criteria_range is paired with the criterion that tests it. At least one pair is required. Additional pairs are optional; all ranges must have matching dimensions.
SUMIFS begins with the sum range, unlike SUMIF. Write the argument roles on paper if you find yourself swapping them.
Worked example: two business dimensions
=SUMIFS(E2:E7,B2:B7,"East",C2:C7,"Online") returns 180. =COUNTIFS(B2:B7,"East",C2:C7,"Online") returns 2.
Add a Revenue condition of ">=100" and only S101 remains: count 1 and revenue 120. Compare the row IDs with the original table before trusting a report built from a longer formula.
Use two criteria on one range for a band
To count revenues from 80 through 150 inclusive, use =COUNTIFS(E2:E7,">=80",E2:E7,"<=150"). The result is 3: 80, 120 and 150. Repeating the same criteria range is intentional.
A range beginning one row late can attach a region to the wrong sale even when dimensions match. Consistent Excel tables help ranges grow together, but the logic still needs inspection. These functions work in current web Excel and established desktop editions.
Build OR logic with attention to overlap
East revenue or West revenue can be added as two SUMIF results, giving 380 + 230 = 610. A single Region cell cannot be both East and West, so these groups do not overlap.
East or Online is different: S101 and S105 meet both conditions. Adding both group totals would count those records twice. Use an explicit nonoverlapping design or subtract the overlap. Write down which records should qualify before choosing a formula.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
The bounded practice checker accepts equivalent supported formulas, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
SUMIFS and COUNTIFS combine criteria with AND. Range alignment and overlapping OR groups deserve explicit checks.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.