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 below has its own stated task and checks; choosing an example here leaves that worksheet and your progress unchanged.
Open the primary practice ↓Microsoft function reference ↗ · Browser calculations checked. Native Excel and Google Sheets recalculation of these reference files has not been verified.
How 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.