You need to total all sales for a specific region during a specific month. Or sum all expenses for one department that fall above a certain amount. SUMIF handles one condition — SUMIFS handles multiple conditions at once, and it’s one of the most useful formulas for building summary reports in Excel.
The Formulas
SUMIF — one condition
=SUMIF(criteria_range, criteria, sum_range)
SUMIFS — multiple conditions
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
Note: In SUMIFS the sum_range comes first, which is the opposite of SUMIF. This trips up a lot of people.
The Scenario
You have a transaction log and need to build a summary showing total sales by region and month.
Transaction Data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Region | Product | Amount |
| 2 | 1/5/2025 | East | Widget A | $1,200 |
| 3 | 1/12/2025 | West | Widget B | $850 |
| 4 | 1/18/2025 | East | Widget A | $2,100 |
| 5 | 2/3/2025 | East | Widget C | $975 |
| 6 | 2/8/2025 | West | Widget A | $1,650 |
| 7 | 2/14/2025 | East | Widget B | $3,200 |
| 8 | 2/22/2025 | West | Widget C | $440 |
| 9 | 3/1/2025 | East | Widget A | $1,800 |
Example 1: SUMIF — Total by Region
Sum all sales for the East region:
=SUMIF(B:B, "East", D:D)
B:B = check the Region column | “East” = match this value | D:D = sum the Amount column. Result: $9,275
Example 2: SUMIFS — Total by Region AND Month
Sum all East region sales in February only:
=SUMIFS(D:D, B:B, "East", A:A, ">="&DATE(2025,2,1), A:A, "<"&DATE(2025,3,1))
D:D = sum Amount | B:B, "East" = Region must be East | A:A, ">="&DATE(2025,2,1) = on or after Feb 1 | A:A, "<"&DATE(2025,3,1) = before Mar 1. Result: $4,175
Date filtering trick
To filter a month, use two date criteria: greater than or equal to the first day AND less than the first day of the next month. This captures every date in the month regardless of time components.
Example 3: SUMIFS — Total by Region AND Product
Sum all East region sales of Widget A:
=SUMIFS(D:D, B:B, "East", C:C, "Widget A")
Result: $5,100 (rows 2, 4, and 9)
Building a Summary Table
Combine SUMIFS with a simple layout to build a summary report that updates automatically when new data is added:
| A | B | C | |
|---|---|---|---|
| 1 | Region | January | February |
| 2 | East | $3,300 | $4,175 |
| 3 | West | $850 | $2,090 |
Each cell uses the same SUMIFS pattern with the row's region and the column's date range. Once built, this table recalculates automatically whenever you add new transactions to the data sheet.
SUMIFS with Wildcards
Need to sum all products that start with "Widget"? Use an asterisk wildcard:
=SUMIFS(D:D, C:C, "Widget*")
Matches "Widget A", "Widget B", "Widget C", or any product starting with "Widget".
COUNTIFS and AVERAGEIFS
The same multi-criteria logic works for counting and averaging:
=COUNTIFS(B:B, "East", A:A, ">="&DATE(2025,2,1), A:A, "<"&DATE(2025,3,1))
Counts how many East region transactions occurred in February. Result: 2
=AVERAGEIFS(D:D, B:B, "East")
Average sale amount for the East region. Result: $1,855
Common mistake
In SUMIFS, every criteria range must be the same size. If your Region column is B2:B100, your Amount column must also be exactly the same number of rows (D2:D100), not D:D. Mismatched ranges cause #VALUE! errors.
When Formulas Aren't Enough
SUMIFS is great for predefined summary tables, but many businesses need more than static formulas can offer:
- Dynamic date ranges — reports that auto-detect the current month, quarter, or fiscal year
- Dozens of categories — manually writing SUMIFS for 50 product lines or 30 regions is tedious and error-prone
- Multi-sheet consolidation — summing across tabs for each department or branch location
- Formatted output — SUMIFS gives you numbers, but leadership wants charts, conditional formatting, and print-ready reports
- One-click refresh — paste new data, click a button, and the entire report rebuilds
A custom VBA report generator handles all of this — dynamic date logic, automatic category detection, multi-sheet consolidation, formatted output, and one-click execution.
"Excellent skillset. Went above and beyond."
Report Client · Upwork
Building the Same Report Every Month?
Tell us about your reporting process and we'll show you how to automate it.
Start a Project