How to sum values by month
✓ Verified in LibreOffice 25.8.7.3Total a column of dated amounts for one month — monthly spend, sales, or hours.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =SUMPRODUCT((MONTH(A2:A5)=7)*(YEAR(A2:A5)=2026)*B2:B5) | Or SUMIFS with date bounds: =SUMIFS(B:B,A:A,">="&DATE(2026,7,1),A:A,"<"&DATE(2026,8,1)). |
| Google Sheets | =SUMPRODUCT((MONTH(A2:A5)=7)*(YEAR(A2:A5)=2026)*B2:B5) | Identical; the SUMIFS date-bounds form also works. |
| LibreOffice Calc | =SUMPRODUCT((MONTH(A2:A5)=7)*(YEAR(A2:A5)=2026)*B2:B5) | Identical. |
How it works
MONTH extracts 1–12 from each date, the comparison makes a 1/0 array, and SUMPRODUCT uses it to keep only July rows: here the two July dates carry 100 + 25 = 125. (In the verified example the dates are raw serial numbers — 46204 is July 1, 2026 — which is exactly what a formatted date cell contains underneath.) Include the YEAR test when data spans years, or use the SUMIFS date-range form, which is faster on big columns and doesn't need array evaluation.
Verified, not just documented
We ran =SUMPRODUCT((MONTH(A2:A5)=7)*B2:B5) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 125 — exactly the expected result. Every formula here is confirmed by actually executing it.