How to Use COUNTIF and SUMIF in Excel (Simple Guide)
How many invoices are unpaid? What's the total of sales in the North region? COUNTIF and SUMIF answer questions like these in one formula — no filtering, no manual adding up.
For the examples below, imagine a sales sheet:
| A: Date | B: Region | C: Status | D: Amount |
|---|---|---|---|
| 01/09 | North | Paid | 250 |
| 03/09 | South | Unpaid | 400 |
| 05/09 | North | Unpaid | 120 |
COUNTIF: count the rows that match
=COUNTIF(range, criteria)
How many invoices are unpaid?
=COUNTIF(C2:C100, "Unpaid")
SUMIF: add up the rows that match
=SUMIF(range to check, criteria, range to add up)
What's the total of unpaid invoices?
=SUMIF(C2:C100, "Unpaid", D2:D100)
Excel checks column C for "Unpaid" and adds up the matching amounts from column D.
Criteria examples
The criteria always go in quotes (unless you're pointing to a cell).
| Criteria | Matches |
|---|---|
| "Paid" | Exactly "Paid" (not case-sensitive) |
| "Paid" | Anything except "Paid" |
| ">100" | Numbers over 100 |
| " | Zero or less |
| "London" | Text containing "London" anywhere |
| "A*" | Text starting with A |
| "" | Blank cells |
| E1 | Whatever is typed in cell E1 |
Use a cell for the criteria
Rather than typing the value into the formula, put it in a cell so you can change it easily. For "equals", just point at the cell:
=COUNTIF(B2:B100, G1)
For "greater than" a cell's value, join the symbol and the cell with &:
=COUNTIF(D2:D100, ">"&G2)
More than one condition: COUNTIFS and SUMIFS
Total of unpaid invoices in the North region:
=SUMIFS(D2:D100, B2:B100, "North", C2:C100, "Unpaid")
Watch out: in SUMIFS the range to add up comes first, then pairs of range and criteria. (In SUMIF it comes last — easy to mix up.)
Count of unpaid invoices over £200:
=COUNTIFS(C2:C100, "Unpaid", D2:D100, ">200")
Between two dates
Total sales in September 2026:
=SUMIFS(D2:D100, A2:A100, ">="&DATE(2026,9,1), A2:A100, "<"&DATE(2026,10,1))
Related functions
• AVERAGEIF / AVERAGEIFS — average of matching rows.
• MAXIFS / MINIFS — largest or smallest matching value.
• MAXIFS / MINIFS — largest or smallest matching value.
They all follow the same pattern.
Troubleshooting
The result is 0. Check for extra spaces in your data ("Paid " isn't "Paid"), or numbers stored as text.
#VALUE! in SUMIFS. All the ranges must be the same size — D2:D100 and B2:B90 won't work.
Criteria with a symbol don't work. Make sure it's all in quotes: ">100", not >100.
Wrapping up
COUNTIF to count, SUMIF to add up, and the -IFS versions when you need more than one condition. Keep your criteria in cells and your summary updates itself when you change them.
