The end date is counted twice
Using <= with the next period’s first day includes a record that belongs to the next period.
Sum amounts that meet several conditions
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
In G7, total amounts where region and status match G2:G3 and the date is at least G4 but before G5.
Follow the highlighted cells. Switch to Your turn to edit. Answer cell: G7. Browser calculations may differ from your Excel version.
Matching rows
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Add paid sales for one region and date window, while keeping refunds and boundary dates in view.
Choose the result you need
Every row must match the region and payment status, start on or after G4, and fall before G5. The matching refund reduces the total to 430.
=SUMIFS(D2:D9,B2:B9,G2,C2:C9,G3,A2:A9,">="&G4,A2:A9,"<"&G5)This is the separate primary practice rule. Dates and amounts are numbers; the start is included and the end excluded. Choosing an exclusion example below does not change that practice.
All result formulas
- G7
=SUMIFS(D2:D9,B2:B9,G2,C2:C9,G3,A2:A9,">="&G4,A2:A9,"<"&G5)
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Date serial" | "Region" | "Status" | "Amount" | "Condition" | "Value" |
| 2 | 46296 | "East" | "Paid" | 120 | "Region" | "East" |
| 3 | 46297 | "East" | "Pending" | 80 | "Status" | "Paid" |
| 4 | 46300 | "West" | "Paid" | 200 | "Start (inclusive)" | 46296 |
| 5 | 46301 | "East" | "Paid" | 250 | "End (exclusive)" | 46327 |
| 6 | 46310 | "East" | "Paid" | -30 | — | — |
| 7 | 46326 | "East" | "Paid" | 90 | "Matching total" | 430 |
| 8 | 46327 | "East" | "Paid" | 400 | — | — |
| 9 | 46295 | "East" | "Paid" | 500 | — | — |
These are reference examples with matching downloads. The primary worksheet has its own stated task and checks; choosing an example here leaves that worksheet and your progress unchanged.
Open the primary practiceMicrosoft function reference ↗ · Browser calculations checked. Native Excel and Google Sheets recalculation of these reference files has not been verified.
All worked examples
Each example uses the source data in its own downloadable workbook. Cell references below belong to that file. Opening an example leaves the primary practice and your progress unchanged.
Sum paid sales in a date window
Every row must match the region and payment status, start on or after G4, and fall before G5. The matching refund reduces the total to 430.
=SUMIFS(D2:D9,B2:B9,G2,C2:C9,G3,A2:A9,">="&G4,A2:A9,"<"&G5)Enter in G7 in this example's workbook. The download contains the complete source and all result formulas.
- G7
- 430
This is the separate primary practice rule. Dates and amounts are numbers; the start is included and the end excluded. Choosing an exclusion example below does not change that practice.
Download this example's workbookExclude one status using a cell
Join the not-equal operator to G2. Every nonblank status except Pending contributes. Column C explains each row; column D retains matching refunds and zeroes.
=SUMIFS($B$2:$B$9,$A$2:$A$9,"<>"&$G$2,$A$2:$A$9,"<>")Enter in G7 in this example's workbook. The download contains the complete source and all result formulas.
- G7
- 410
- C2
- true
- D2
- 120
- C3
- false
- D3
- 0
- C4
- true
- D4
- 200
- C5
- true
- D5
- -30
- C6
- false
- D6
- 0
- C7
- true
- D7
- 50
- C8
- true
- D8
- 70
- C9
- true
- D9
- 0
Matching ignores letter case and keeps spaces significant. The separate nonblank criterion deliberately leaves out the empty status. G2 contains plain text without wildcard symbols.
Download this example's workbookExclude two statuses with AND
Require the same status to differ from both G2 and G3. Pending and Cancelled are both excluded. A paid refund still reduces the total.
=SUMIFS($B$2:$B$9,$A$2:$A$9,"<>"&$G$2,$A$2:$A$9,"<>"&$G$3,$A$2:$A$9,"<>")Enter in G7 in this example's workbook. The download contains the complete source and all result formulas.
- G7
- 210
- C2
- true
- D2
- 120
- C3
- false
- D3
- 0
- C4
- false
- D4
- 0
- C5
- true
- D5
- -30
- C6
- false
- D6
- 0
- C7
- true
- D7
- 50
- C8
- true
- D8
- 70
- C9
- true
- D9
- 0
All criteria pairs in this SUMIFS must hold on one row. Repeating the status range is intentional. Blank statuses are explicitly excluded; G2:G3 are plain text labels.
Download this example's workbookSee why adding two exclusions overcounts
Adding a not-Pending subtotal to a not-Cancelled subtotal returns 700. A Paid row enters both subtotals; each excluded label still enters the other branch. Column C counts these inclusions.
=SUMIFS($B$2:$B$9,$A$2:$A$9,"<>"&$G$2,$A$2:$A$9,"<>")+SUMIFS($B$2:$B$9,$A$2:$A$9,"<>"&$G$3,$A$2:$A$9,"<>")Enter in G7 in this example's workbook. The download contains the complete source and all result formulas.
- G7
- 700
- C2
- 2
- D2
- 240
- C3
- 1
- D3
- 80
- C4
- 1
- D4
- 200
- C5
- 2
- D5
- -60
- C6
- 0
- D6
- 0
- C7
- 2
- D7
- 100
- C8
- 2
- D8
- 140
- C9
- 2
- D9
- 0
This is a deliberate incorrect construction for excluding both labels. Use the two-exclusions AND example for that task. Refunds can be counted twice too; the overlap affects signed amounts.
Download this example's workbookSum amounts with a nonblank status
The quoted not-equal operator by itself requests a nonblank status. The invoice with no status contributes nothing, while numeric zero and the matching negative amount remain valid amounts.
=SUMIFS($B$2:$B$9,$A$2:$A$9,"<>")Enter in G7 in this example's workbook. The download contains the complete source and all result formulas.
- G7
- 490
- C2
- true
- D2
- 120
- C3
- true
- D3
- 80
- C4
- true
- D4
- 200
- C5
- true
- D5
- -30
- C6
- false
- D6
- 0
- C7
- true
- D7
- 50
- C8
- true
- D8
- 70
- C9
- true
- D9
- 0
Only the status column is tested. The G2:G3 exclusion controls are not used. A space-only label is text rather than an empty cell; this supplied source uses a genuine blank.
Download this example's workbookExclude a literal label containing a star
Place a tilde before each star in G2. The criterion treats Part*1 as one literal label, so PartX1 still contributes. The resulting total is 440.
=SUMIFS($B$2:$B$9,$A$2:$A$9,"<>"&SUBSTITUTE($G$2,"*","~*"),$A$2:$A$9,"<>")Enter in G7 in this example's workbook. The download contains the complete source and all result formulas.
- G7
- 440
- C2
- true
- D2
- 120
- C3
- true
- D3
- 80
- C4
- true
- D4
- 200
- C5
- true
- D5
- -30
- C6
- false
- D6
- 0
- C7
- false
- D7
- 0
- C8
- true
- D8
- 70
- C9
- true
- D9
- 0
This reference supports labels containing stars but no question marks or tildes. SUBSTITUTE escapes each star before adding <>. Matching ignores letter case and explicitly excludes genuine blank statuses.
Download this example's workbookExclude a wildcard pattern intentionally
Leaving the star unescaped makes Part*1 a pattern. It matches both Part*1 and PartX1 in this source, so both amounts are excluded and the total becomes 370.
=SUMIFS($B$2:$B$9,$A$2:$A$9,"<>"&$G$2,$A$2:$A$9,"<>")Enter in G7 in this example's workbook. The download contains the complete source and all result formulas.
- G7
- 370
- C2
- true
- D2
- 120
- C3
- true
- D3
- 80
- C4
- true
- D4
- 200
- C5
- true
- D5
- -30
- C6
- false
- D6
- 0
- C7
- false
- D7
- 0
- C8
- false
- D8
- 0
- C9
- true
- D9
- 0
Use this example only when pattern matching is intended. It answers a different question from literal-label exclusion. These supplied labels have no other Part-prefix/end-1 matches; blank statuses remain explicitly excluded.
Download this example's workbookHow it works
One row must pass every test
SUMIFS uses AND logic across these criteria pairs. An East record with the wrong status is excluded, as is a paid record from West. To express an OR condition, you would need a different construction.
Why these dates are numbers
Excel stores dates as serial numbers. This example uses the Excel 1900 date system: 46296 is October 1, 2026, and 46327 is November 1, 2026. Keep the start inclusive and the following period’s start exclusive; it also avoids dropping records with times on the final day.
Range and input boundaries
All four data ranges cover rows 2 through 9. Amounts and dates in this practice are numeric; text that only looks like a number needs cleaning first. Region and status labels are matched without regard to case. The example uses plain criteria without wildcard symbols.
Read the full step-by-step walkthrough
- Start with the amounts
The first SUMIFS argument is the range to add. Conditions come after it.
D2:D9 - Keep one region
Compare each region with G2. A row from another region cannot contribute.
B2:B9,G2 - Require the right status too
The status test is another requirement on that same row. Both the region and status must match.
C2:C9,G3 - Include the first day
Join the comparison symbol to the numeric date in G4. Dates here are Excel serial values so the boundary is explicit.
A2:A9,">="&G4 - Stop before the next period
Use an exclusive end. A record exactly on G5 belongs to the next period and is not included.
A2:A9,"<"&G5 - Add the matching amounts
Include refunds as negative amounts. A filter with no matching rows returns zero. Change a condition and compare the rows again.
Things to watch
The total ignores a refund
A negative matching amount is still part of the sum. Do not add an amount>0 condition unless the task calls for gross sales.
The criteria range starts on another row
SUMIFS pairs positions across ranges. Keep the same first and last row for every range.
About this example
Published examples, formula filling and practice checks were tested in Chrome on October 13, 2026. Tests cover the supplied datasets and stated input rules, not every possible formula. These workbooks have not yet been independently recalculated in Microsoft Excel or Google Sheets.
Content version 3. Practice checks compare calculated results across different datasets; they do not certify every possible formula.