Build a conditional sales total
Enter the data below on Sales. Store column C as numbers and column D as real spreadsheet dates, displayed here in year-month-day order.
| Row | A: Region | B: Item | C: Amount | D: Date |
|---|---|---|---|---|
| 2 | East | Notebook | 120 | 2026-10-02 |
| 3 | West | Notebook | 90 | 2026-10-03 |
| 4 | East | Stapler | 80 | 2026-10-04 |
| 5 | East | Notebook | 200 | 2026-11-01 |
| 6 | East | Notebook | 50 | 2026-10-20 |
=SUMIFS(Sales!C2:C6,Sales!A2:A6,"East",Sales!B2:B6,"Notebook")The total is 370: 120 + 200 + 50. SUMIFS starts with the values to add, then pairs each criteria range with its condition. East must match column A and Notebook must match column B on the same row. West sales and East stapler sales are excluded.
Limit the total to October
=SUMIFS(Sales!C2:C6,Sales!A2:A6,"East",Sales!B2:B6,"Notebook",Sales!D2:D6,">="&DATE(2026,10,1),Sales!D2:D6,"<"&DATE(2026,11,1))The result is 170: 120 from October 2 plus 50 from October 20. The 200 sale on November 1 is excluded. An inclusive start and exclusive next-month boundary also works when dates contain a time of day. DATE avoids an ambiguous text date such as 10/1/26.
Turn a fixed example into a summary
On a Summary sheet, set F2 to East and G2 to Notebook. Use fixed data ranges and relative criteria cells:
=SUMIFS(Sales!$C$2:$C$6,Sales!$A$2:$A$6,F2,Sales!$B$2:$B$6,G2)This returns 370. Copying down lets the next summary row use F3 and G3. New sales beyond row 6 are not included in this bounded example; extend all three ranges together when you add records.
Fix the mistakes that change totals
- Wrong argument order: SUMIFS puts the sum range first. SUMIF has a different argument order.
- Different range sizes: C2:C6 and A2:A100 do not describe aligned data. Keep the same row boundaries for each criteria range.
- Text amounts: a value that looks like 120 may be text after an import. Convert the source values to numbers before trusting the sum.
- OR versus AND: each additional pair narrows the qualifying rows. It does not mean either condition can match.
Compare a filtered set of source rows with your expected total before copying the formula into a report. See Microsoft’s SUMIFS reference for the full syntax.