SUMIF & COUNTIF
Translate one condition into a dependable summary.
By the end, you’ll be able to:
- Separate a criteria range from a sum range
- Write text and numeric criteria
- Reconcile a conditional result to its source records
Start with the question, not the function
A sales log has one row per order. To find East revenue, first identify rows whose Region is East, then add Revenue on those rows. That is a conditional sum. To find how many orders belong to East, count those rows instead.
Each record must stay aligned across columns. A region on row 4 belongs to the revenue on row 4. Do not sort only one column or include a separate total row in the source.
| 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 |
Read the arguments in order
=SUMIF(range, criteria, [sum_range])=COUNTIF(range, criteria)range contains the values to test. criteria is the condition, such as "East" or ">=100". sum_range contains corresponding numbers to add; omit it only when testing and summing the same cells. COUNTIF needs no sum range.
Text and comparison criteria go in straight double quotes. A cell reference such as G2 can supply an exact criterion without quotes. Keep the criteria and sum ranges the same size and aligned.
Worked example: East orders
=SUMIF(B2:B7,"East",E2:E7) returns 380: orders S101, S103 and S105 contribute 120 + 200 + 60. =COUNTIF(B2:B7,"East") returns 3.
Those are different measures: revenue is an amount, order count is a number of records. If an order can occupy multiple lines, COUNTIF counts matching lines; it does not suddenly become a count of distinct order IDs. State the grain of your data before naming a measure.
Numeric criteria include the operator
=COUNTIF(E2:E7,">=100") returns 3, including the boundary of 100. =SUMIF(E2:E7,">=100") returns 470, because the tested cells are also the summed cells.
In Excel you can build a changing threshold with ">="&G2; & joins the operator to the cell value. Wildcards such as East* are useful only when partial matching is intended. Our exercises begin with explicit exact labels and numeric thresholds.
Check labels and reconcile totals
Ordinary text criteria are case-insensitive, so east and EAST match East. Extra spaces can still make imported labels differ. Clean source data rather than changing the spelling of criteria until a number happens to look plausible.
East 380 + West 230 + North 40 equals the full 650 revenue. Reconciliation will expose omitted groups, but does not prove every input is correct. These functions are available in established desktop Excel editions and current Excel for the web; local separators may differ.
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 practice checker accepts any supported formula that solves the task, including whole-column ranges where supported, 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
Use SUMIF for an amount and COUNTIF for a record count. Identify the source grain and keep corresponding ranges aligned.
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. Technical review: 2026-10-16. Page updated: 2026-10-19. How SheetSprout develops and checks lessons.