📎 This article includes 1 downloadable practice file ↓
If you only learn one family of Excel formulas for reporting, make it this one. SUMIFS, COUNTIFS and AVERAGEIFS answer questions like “how much did the North region sell to retail customers in March?” — without a pivot table.
In this article
- The pattern
- Sample data
- 12 examples
- 1. One condition
- 2. Two conditions
- 3. Criteria from cells (build a report grid)
- 4. Greater than / less than
- 5. A date range (one month)
- 6. Month from a cell
- 7. Not equal
- 8. Wildcards
- 9. Blank and non-blank
- 10. OR logic (North or East)
- 11. Average with conditions
- 12. Count distinct-ish: rows meeting all conditions
- Common mistakes
- SUMIFS or a pivot table?
The pattern
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
=COUNTIFS(criteria_range1, criteria1, ...)
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)Every condition must be true for a row to count (AND logic). All ranges must be the same size.
Sample data
Sheet Sales: A = Date, B = Region, C = Customer type, D = Product, E = Amount.
12 examples
1. One condition
=SUMIFS(E:E, B:B, "North")2. Two conditions
=SUMIFS(E:E, B:B, "North", C:C, "Retail")3. Criteria from cells (build a report grid)
=SUMIFS($E:$E, $B:$B, $H2, $C:$C, I$1)Put regions down column H and customer types across row 1, write the formula once and fill it across the grid. The $ signs keep each reference pointing at the right header.
4. Greater than / less than
=COUNTIFS(E:E, ">=50000")
=SUMIFS(E:E, E:E, ">"&K1) ' threshold typed in K1Operators go inside quotes; join them to a cell with &.
5. A date range (one month)
=SUMIFS(E:E, A:A, ">="&DATE(2017,3,1), A:A, "<"&DATE(2017,4,1))Using “less than the first of next month” avoids problems with times on the last day.
6. Month from a cell
=SUMIFS(E:E, A:A, ">="&K2, A:A, "<"&EDATE(K2,1)) ' K2 = 01-Mar-20177. Not equal
=SUMIFS(E:E, B:B, "<>South")8. Wildcards
=SUMIFS(E:E, D:D, "Laptop*") ' starts with Laptop
=COUNTIFS(D:D, "*cable*") ' contains cable9. Blank and non-blank
=COUNTIFS(C:C, "") ' customer type missing
=COUNTIFS(C:C, "<>") ' filled in10. OR logic (North or East)
=SUM(SUMIFS(E:E, B:B, {"North","East"}))The array constant runs SUMIFS twice; SUM adds the two results.
11. Average with conditions
=AVERAGEIFS(E:E, B:B, "North", C:C, "Retail")12. Count distinct-ish: rows meeting all conditions
=COUNTIFS(B:B, "North", E:E, ">0", A:A, ">="&DATE(2017,1,1))Common mistakes
| Mistake | Fix |
|---|---|
| Ranges of different sizes (E2:E500 vs B2:B400) | Use full columns or identical ranges — mismatched ranges give #VALUE! |
| Numbers stored as text | Convert with Text to Columns or --A2; SUMIFS skips text numbers |
| Trailing spaces in names | Clean with TRIM — “North ” is not “North” |
Writing ">=" DATE(...) without & |
Always join operator and value: ">="&DATE(...) |
=SUMIFS(Sales[Amount], Sales[Region], "North"). The formula keeps working as rows are added.SUMIFS or a pivot table?
Use a pivot table to explore; use SUMIFS when the report layout is fixed (a monthly MIS format your manager expects) and must update automatically.
📎 Practice files for this article
- 📗SUMIFS/COUNTIFS scenarios workbookConditional sums and counts with dates, OR logic and wildcards, plus 4 classic reasons totals come out wrong — all with PASS/FAIL checks.⬇ XLSX · 9 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.