1
00:00:00,080 --> 00:00:04,142
A monthly total is easy to overstate when a category repeats

2
00:00:04,142 --> 00:00:07,834
outside the month or other categories share the same dates.

3
00:00:07,834 --> 00:00:11,526
This fictional ledger asks one question: how much Software spend

4
00:00:11,526 --> 00:00:13,742
occurred in September twenty twenty-six?

5
00:00:13,742 --> 00:00:17,434
The correct answer is seventy-five dollars from three rows.

6
00:00:17,434 --> 00:00:21,495
We will use SUMIFS with a category match and two date

7
00:00:21,495 --> 00:00:24,080
boundaries, then verify the contributing rows independently.

8
00:00:24,680 --> 00:00:29,058
The eight-row practice file uses DATE formulas and US comma separators.

9
00:00:29,058 --> 00:00:33,070
In a disposable Google Sheet, choose File, Settings, and set that

10
00:00:33,070 --> 00:00:37,448
spreadsheet's locale to United States; your account language does not change.

11
00:00:37,448 --> 00:00:42,190
Paste the file into A one and format the Date column if needed.

12
00:00:42,190 --> 00:00:46,203
Before summing, check that ISNUMBER of A two returns TRUE; otherwise

13
00:00:46,203 --> 00:00:48,027
re-enter the date formulas.

14
00:00:48,627 --> 00:00:52,522
Before writing a formula, mark September first, twelfth, and thirtieth

15
00:00:52,522 --> 00:00:54,079
as the Software matches.

16
00:00:54,079 --> 00:00:59,142
Their amounts are twenty-four, thirty-six, and fifteen dollars, totaling seventy-five.

17
00:00:59,142 --> 00:01:03,425
The October first Software row is outside September; so is the

18
00:01:03,425 --> 00:01:05,373
August thirty-first Software row.

19
00:01:05,373 --> 00:01:08,877
September Hosting and Office entries also do not qualify.

20
00:01:08,877 --> 00:01:14,329
This independent row test catches a formula that uses only category or only date.

21
00:01:14,929 --> 00:01:18,574
Google describes SUMIFS as summing one range when each specified

22
00:01:18,574 --> 00:01:21,125
criterion is met in the same row.

23
00:01:21,125 --> 00:01:25,133
In our sheet, C two through C nine contains amounts, B

24
00:01:25,133 --> 00:01:30,236
two through B nine contains categories, and A two through A nine contains dates.

25
00:01:30,236 --> 00:01:32,787
These ranges must have matching row boundaries.

26
00:01:32,787 --> 00:01:36,795
We will test category equals Software, date on or after September

27
00:01:36,795 --> 00:01:38,982
first, and date before October first.

28
00:01:39,582 --> 00:01:43,247
Type the formula from the guide into an empty cell.

29
00:01:43,247 --> 00:01:49,111
The first date test joins greater-than-or-equal with DATE twenty twenty-six, nine, one.

30
00:01:49,111 --> 00:01:50,577
That includes September first.

31
00:01:50,577 --> 00:01:54,974
A condition written simply as greater-than would miss the first day.

32
00:01:54,974 --> 00:01:57,906
The category test uses the exact text Software.

33
00:01:57,906 --> 00:02:01,571
The DATE function creates a date value, avoiding region-dependent

34
00:02:01,571 --> 00:02:04,137
interpretation of a month-slash-day string.

35
00:02:04,737 --> 00:02:09,722
The second date test is less than October first, not less than or equal.

36
00:02:09,722 --> 00:02:13,638
That excludes an October first entry and still includes every September

37
00:02:13,638 --> 00:02:15,063
date, including the thirtieth.

38
00:02:15,063 --> 00:02:19,336
Using the next month's first day is easier to maintain than

39
00:02:19,336 --> 00:02:21,828
guessing the last day of each month.

40
00:02:21,828 --> 00:02:25,745
It also works when dates contain times, because all times before

41
00:02:25,745 --> 00:02:27,169
October first remain included.

42
00:02:27,769 --> 00:02:30,097
The formula should return seventy-five.

43
00:02:30,097 --> 00:02:34,752
If it returns two hundred sixty-two, you probably summed all Software

44
00:02:34,752 --> 00:02:36,692
rows without the month bounds.

45
00:02:36,692 --> 00:02:41,347
If it returns one hundred forty-seven, you probably summed all September

46
00:02:41,347 --> 00:02:42,899
rows without the category.

47
00:02:42,899 --> 00:02:46,778
Compare the three contributing rows in the downloadable answer check.

48
00:02:46,778 --> 00:02:51,046
Then change the category criterion to Hosting; the same September date

49
00:02:51,046 --> 00:02:52,985
window should return sixty dollars.

50
00:02:53,585 --> 00:02:57,645
If the result is zero, test ISNUMBER on a date cell

51
00:02:57,645 --> 00:03:01,335
and confirm the DATE formulas evaluated instead of remaining text.

52
00:03:01,335 --> 00:03:05,394
Check the sum and criterion ranges all end on row nine.

53
00:03:05,394 --> 00:03:09,454
If your spreadsheet uses another locale, set this practice Sheet to

54
00:03:09,454 --> 00:03:11,668
United States or adapt the separators.

55
00:03:11,668 --> 00:03:16,096
The seventy-five-dollar result was checked in a private fictional Sheet;

56
00:03:16,096 --> 00:03:18,311
these on-screen panels remain illustrations.