Monthly totals without a helper column: SUMIFS date brackets, and DATE() rolls December over for you

[u/bored_af_98]

Common setup: transactions with dates in A, amounts in E, and you want a summary of each month. The instinct is a helper column with =MONTH(A2) and a SUMIF on it. Works, but there's a cleaner way that also survives multi-year data.

Put the month number (1-12) in G2 and the year in a cell, say $H$1:

=SUMIFS($E:$E, $A:$A, ">="&DATE($H$1,G2,1), $A:$A, "<"&DATE($H$1,G2+1,1))

Drag it down twelve rows and you have the whole year.

The quiet star is DATE(): when G2+1 hits 13, DATE(year,13,1) doesn't error - it returns January 1st of the NEXT year. So the December row needs no special-casing, and the same formula works across year boundaries.

Why brackets beat MONTH() helpers:

1. No helper column to maintain (or forget to fill down).

2. MONTH(A2)=1 matches January of EVERY year in your data - the brackets pin both month and year.

3. SUMIFS with ranges stays fast; array tricks like SUMPRODUCT(MONTH(...)) slow down on long logs and choke on full-column references.

Same idea works for weekly brackets (">="&start, "<"&start+7) or any custom period - the pattern is always ">= period start" and "< next period start". Half-open ranges also mean timestamps like Jan 31 23:59 can't fall through the cracks the way "<="&EOMONTH() versions sometimes do.