To total values on more than one condition in Excel, use =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2). Note the argument order differs from SUMIF: in SUMIFS the range being summed comes first, in SUMIF it comes last. A dated example: =SUMIFS(D:D, B:B, "East", A:A, ">="&DATE(2026,2,1)). COUNTIFS and AVERAGEIFS take the same arguments and return counts and averages instead.
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.
SUMIF and SUMIFS syntax compared
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.
Example: regional sales transactions
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/2026 | East | Widget A | $1,200 |
| 3 | 1/12/2026 | West | Widget B | $850 |
| 4 | 1/18/2026 | East | Widget A | $2,100 |
| 5 | 2/3/2026 | East | Widget C | $975 |
| 6 | 2/8/2026 | West | Widget A | $1,650 |
| 7 | 2/14/2026 | East | Widget B | $3,200 |
| 8 | 2/22/2026 | West | Widget C | $440 |
| 9 | 3/1/2026 | 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(2026,2,1), A:A, "<"&DATE(2026,3,1))
D:D = sum Amount | B:B, “East” = Region must be East | A:A, “>=”&DATE(2026,2,1) = on or after Feb 1 | A:A, “<“&DATE(2026,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. Enter the month headers as real dates (1/1/2026 in B1, 2/1/2026 in C1) with the custom number format mmmm, so they show January and February. With the transactions on a sheet named Data, B2 is:
=SUMIFS(Data!$D:$D, Data!$B:$B, $A2, Data!$A:$A, ">="&B$1, Data!$A:$A, "<"&EDATE(B$1,1))
Fill it right and down. $A2 keeps the region column, B$1 keeps the header row, and EDATE(B$1,1) is the first day of the next month.
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(2026,2,1), A:A, "<"&DATE(2026,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.
Common questions
How do I total on either of two conditions rather than both?
SUMIFS always combines criteria with AND. For OR, add two SUMIFS together, or use SUMPRODUCT with the conditions added rather than multiplied.
How do I use a date as a criterion?
Join the operator to the date with an ampersand: ">="&DATE(2026,2,1). Typing the date directly inside the quotes will not work reliably.
Why does SUMIFS return zero when the data is clearly there?
The criteria do not match exactly. Trailing spaces, numbers stored as text, and dates stored as text are the three usual causes, in that order.
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.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.