Google Sheets: Sum by Month and Category Without Mixing Years

Prepare numeric timestamps and amounts

Import monthly-category-input.csv into a working sheet so its header starts at A1 and its eight records occupy A2:C9. A is booked_at, B is category, and C is amount. Keep the original download unchanged. The calculation requires numeric date-time values in A and numeric amounts in C. Check =ISNUMBER(A2) and =ISNUMBER(C2), then fill those checks down. A date-looking display does not prove that its underlying value is numeric.

For this fixed-width sample only, text timestamps can be converted in D2 with the formula below. Fill D2:E2 down only for rows whose A value is text in the exact YYYY-MM-DD hh:mm:ss shape. Every E result must be TRUE before copying the corresponding D values and pasting values only back into the working copy of A. DATE and TIME can normalize invalid components, so the round-trip comparison matters. Do not apply this substring formula to an already numeric timestamp, UTC text ending in Z, offset timestamps or fractional seconds.

D2: =DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(MID(A2,9,2)))+TIME(VALUE(MID(A2,12,2)),VALUE(MID(A2,15,2)),VALUE(RIGHT(A2,2)))
E2: =IFERROR(TEXT(D2,"yyyy-mm-dd hh:mm:ss")=A2,FALSE)

Check missing values before trusting a total

After converting timestamp text, use the row check below in F2 and fill it through F9. All eight sample records should show Ready. A text amount such as a leading-apostrophe 40, an absent timestamp, a blank category or a blank amount needs review. Formatting a cell as Currency or Date is not a data conversion. A numeric zero is valid, and the negative adjustment must stay signed.

Review is a stop-and-check flag, not permission to delete the transaction or turn missing amounts into zero. Resolve missing values from the source and agree whether a genuinely absent amount means zero or unknown. The nonempty-category check does not validate spelling: keep Travel and Office consistent, without extra spaces. This example uses plain category names; names containing * or ? need a separate literal-match check because SUMIFS criteria can be patterns.

=IFERROR(IF(AND(ISNUMBER(A2),LEN(TRIM(B2))>0,ISNUMBER(C2)),"Ready","Review"),"Review")

Build the month and category grid

Use G1:I5 for the summary. Put month_start in G1, Travel in H1, and Office in I1. In G2:G5 enter =DATE(2025,1,1), =DATE(2026,1,1), =DATE(2026,2,1), and =DATE(2026,3,1). Each boundary must be the first day of its month at midnight. A midmonth date or a time component would make this a rolling interval instead of a calendar-month total.

Format the month column as mmm yyyy if you want compact labels. Keep the underlying dates; text labels such as Jan omit the year and are not a dependable boundary value. The expected CSV is a reference for this summary grid, not another transaction file.

Enter SUMIFS once and fill across and down

Enter the formula in H2, fill it across to I2, then fill H2:I2 down through row 5. The amount, category, and timestamp source ranges stay fixed and cover the same eight records. H$1 changes to I$1 when copied right while keeping row 1; $G2 changes to $G3 when copied down while keeping column G. H3 is January 2026 Travel, so it should return 90.

The formula uses commas between arguments. Depending on your locale, the formula editor may require semicolons. The month boundary remains a date calculation, so it does not depend on spelling January in a particular language.

=SUMIFS($C$2:$C$9,$B$2:$B$9,H$1,$A$2:$A$9,">="&$G2,$A$2:$A$9,"<"&EDATE($G2,1))

Include the whole last day

The January 2026 Travel amount is 40 + 60 - 10 = 90. The 60 posted at 23:59:59 on January 31 must be included. A test that ends at less than or equal to January 31's midnight can miss it, because the timestamp is later than that date's start. In this fixture, <=DATE(2026,1,31) gives only 30 for January 2026 Travel; the correct <DATE(2026,2,1) upper boundary gives 90.

Using less than February 1 avoids guessing the last possible second or fractional second in January. Exactly midnight on February 1 is excluded from January and included in February. The same rule works across month lengths and the December-to-January year boundary.

Keep the reporting time zone consistent

These sample timestamps represent local wall-clock time in one agreed reporting zone. They contain no UTC marker or offset. If your source is UTC and the report uses another zone, convert the timestamps into the reporting zone before applying month boundaries. An event near midnight can belong to a different local date.

Check File > Settings for the spreadsheet's locale and time zone, but do not assume a settings change rewrites arbitrary timestamp text from another system. Keep the source convention with the data. Also keep refunds or adjustments signed: the negative Travel amount reduces the total instead of being dropped.

Extend every range when new transactions arrive

The worked formula deliberately stops at row 9. In a practice copy, put =DATE(2026,1,15) in A10, Travel in B10, and numeric 5 in C10. H3 remains 90 until all three source ranges are expanded: $C$2:$C$10, $B$2:$B$10 and $A$2:$A$10 (both timestamp criteria use that last range). Also fill the row-readiness check to F10.

After updating the formula and filling the summary again, January 2026 Travel is 95 and the full summary is 370. Other cells retain their baseline values. Undo this optional extra row when comparing against the expected CSV, which contains only the original eight records. For a recurring report, choose an agreed larger range or maintained table reference and ensure every source column covers the same rows.

Check a zero or an unexpectedly large result

For the fixture, a zero for January 2026 Travel is wrong; start by inspecting numeric types, exact category spelling, and the month-start cell. A large January figure may indicate that several years were combined. Different-sized or shifted criteria ranges can also associate dates with the wrong amount.

For the unmodified fixture, =SUM(H2:I5) and =SUM(C2:C9) should both return 365. January 2026 Travel is 90; February 2026 Travel is 80. Inspect the last-second and exact-midnight records separately. Reconciliation is a check, not proof: omissions and duplicates can cancel each other, and a real summary must cover all intended months and categories. If you need to discover all categories automatically rather than maintain this fixed grid, the separate QUERY GROUP BY guide explains that different output.

Related help: Build a dynamic grouped summary with QUERY · Check wildcard patterns versus literal category codes

Verified practice results

This synthetic example was calculated in Google Sheets on October 11, 2026. All eight month/category results matched the expected grid, totaling 365. January 2026 Travel was 90 with the next-month boundary and only 30 with the incorrect last-day-midnight boundary; combining January by month number alone returned 180. The valid timestamp conversions and Ready checks also passed, and the grid was inspected in the browser.

Adding the optional five-unit transaction left the fixed-range total at 90; extending every source range changed it to 95 and the full summary to 370. Removing the practice addition restored the baseline. Separate native checks marked missing values, a text amount and a source error for review while accepting zero and a negative amount. February 30 normalized to March 2 and failed the text round-trip check. These observations cover this fixed-format fixture and controlled input changes, rather than arbitrary timestamp formats, every locale or time-zone conversion.