📎 This article includes 1 downloadable practice file ↓
In this article
Most “reports” in an office are the same question asked with different filters: sales of North, sales in May, pending invoices of Neha. The -IFS family answers all of them from one raw list, and the answers update the moment the data does.
The pattern
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
=COUNTIFS(criteria_range1, criteria1, ...)
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)Every criterion is an AND. All ranges must be the same height.
Criteria you’ll use every day
| Need | Criteria |
|---|---|
| Exact text | "North" or a cell: H2 |
| Not equal | "<>Paid" |
| Above / below | ">5000" or ">="&H3 |
| Contains | "*pen*" (wildcards * and ?) |
| Date from-to | ">="&DATE(2026,5,1) and "<="&DATE(2026,5,31) on the same date column |
| Blank / not blank | "" / "<>" |
A month × region grid in one formula
Put months (as the 1st of each month) down column A and regions across row 1. In B2:
=SUMIFS(Sales!$K:$K, Sales!$D:$D, B$1, Sales!$B:$B, ">="&$A2, Sales!$B:$B, "<="&EOMONTH($A2,0))Fill right and down. The $ signs keep the ranges fixed while the region and month references move. This is how to build a PivotTable-style summary that you can format freely.
=SUM(SUMIFS(Amount,Region,{"North","East"})).Common mistakes
- Sum range at the end, as in SUMIF. In SUMIFS it comes first.
- Typing the operator inside the date:
">=01/05/2026"depends on your Windows date setting. Build it with DATE. - Numbers stored as text are skipped by SUMIFS. Convert the column (Data > Text to Columns > Finish).
Practice
Download this lesson’s workbook below. All questions use the same Sales sheet. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.
📎 Practice files for this article
- 📗Lesson 2 practice workbookSales register + 8 report questions with checks.⬇ XLSX · 18 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.