Math & Trig
Sum by month and year with SUMIFS and EOMONTH
Aggregate daily transaction logs into monthly totals dynamically from a Year and Month cell without helper columns.
Bound a calendar month cleanly with DATE and EOMONTH
Summing by month Number alone accidentally merges March 2025 with March 2026. Building the exact first day mStart = DATE(Yr, Mo, 1) and last day EOMONTH(mStart, 0) isolates one specific reporting month.
Use >= the first day and < the first day of the next month. This includes all times on the final day, unlike <= EOMONTH, which stops at midnight.
| TxDate (A) | Revenue (B) | Target: 2026-03 |
|---|---|---|
| 2025-03-15 | 400 | Excluded (2025) |
| 2026-03-01 | 550 | Included |
| 2026-03-31 | 750 | Included |
| 2026-04-01 | 300 | Excluded (April) |
Monthly SUMIFS formula
Bind mStart = DATE(C2, D2, 1) with LET so the month start date is constructed once.
=LET(m, DATE(C2, D2, 1), SUMIFS(B2:B5, A2:A5, ">=" & m, A2:A5, "<" & (EOMONTH(m, 0)+1)))Monthly transaction count
Replace SUMIFS with COUNTIFS to count orders placed in the target month.
=LET(m, DATE(C2, D2, 1), COUNTIFS(A2:A5, ">=" & m, A2:A5, "<" & (EOMONTH(m, 0)+1)))Inputs and interpretation
Use numeric spreadsheet dates/timestamps in one consistent time zone and numeric amounts. Validate month as an integer 1–12 and year within the supported date range; DATE otherwise normalizes out-of-range months. Text dates require parsing before comparison.
Try the example
Use the original values or change them to test your understanding.