Try the example, then check the result.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
This fictional ledger asks: What was Software spend in September 2026? The independent answer is $75, from three rows. The paste-ready transaction table includes distracting August and October Software rows plus other September categories. The answer check shows every included and excluded row. The companion video's Sheets panels are illustrative diagrams; the result was also calculated in a private fictional Sheet.
Prepare a disposable Sheet
- Open a blank Google Sheet for practice. Choose File → Settings → Locale → United States, then save and reload. The comma-separated DATE and SUMIFS formulas below were verified with this spreadsheet locale; it does not change your Google Account's interface language. If you keep another locale, adapt formula separators before entering them. Copy
ledger-paste.txtand paste it into A1. You should have columns Date, Category, and Amount (USD), with eight transactions in rows 2–9. Do not paste over a real ledger. - The Date cells contain
DATEformulas. If they display numbers, format column A as Date. Check=ISNUMBER(A2)in a spare cell; it should return TRUE. If the formula text is visible instead of a date, re-enter the date formulas in A2:A9 directly. Use an English-US Sheets locale for the formula punctuation shown below, or adapt function names and separators to your locale. - Read the answer check before calculating: only September 1 ($24), September 12 ($36), and September 30 ($15) are both Software and in September. Their sum is $75.
Enter the formula
In an empty cell, enter:
=SUMIFS(C2:C9,B2:B9,"Software",A2:A9,">="&DATE(2026,9,1),A2:A9,"<"&DATE(2026,10,1))C2:C9 is the amount range. The category range B2:B9 must equal Software. Two checks on A2:A9 include dates on or after September 1 and before October 1. Google documents that SUMIFS includes a row only when all criteria match. The next-month upper bound is exclusive, so September 30 is in and October 1 is out. DATE creates numeric dates without relying on the interpretation of a typed date string.
Verify the result
The expected result is $75. Compare its three contributing rows with the answer check. Then change "Software" to "Hosting" temporarily; the expected September result is $60 from $40 and $20. Restore Software afterward.
| Result | Likely issue |
|---|---|
| $262 | Category was checked, but the date boundaries were omitted. |
| $147 | September was checked, but the category was omitted. |
| $0 | Dates may be text, or a criterion range may point to the wrong column. |
| A number other than $75 | Check whether every range covers rows 2–9 and the end test uses < October 1. |
Swipe or scroll sideways to see every table column →
The two wrong totals above are deliberate diagnostics from the sample data, not universal meanings of those numbers. If dates carry times in a real Sheet, an exclusive next-month boundary still includes all September timestamps before October 1.
FAQ
Why not use `<=` October 1? That would include the October 1 Software row and overstate September.
Can I type `9/1/2026` into the formula instead of DATE? Date string interpretation can depend on locale. DATE makes the intended year, month, and day explicit.
Why must the ranges have the same rows? SUMIFS evaluates criteria against matching positions in the amount range. A shifted range can pair the wrong date, category, and amount.
Sources and visual note
Google's spreadsheet locale guidance, SUMIFS reference, DATE reference, and ISNUMBER reference were reviewed on 9 October 2026. The video uses labeled English illustrative diagrams, not app recordings. In a private fictional Sheet with United States locale, the pasted DATE formulas evaluated as numeric dates (ISNUMBER(A2) returned TRUE); the exact SUMIFS returned $75, Hosting returned $60, all Software returned $262, and all September categories returned $147.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.