Home » Posts

Sum by month ignore year

To sum data by month while ignoring year values you can use a formula based on the SUMPRODUCT function and the MONTH function. In the example shown, the formula in F5 is: =SUMPRODUCT((MONTH(date)=MONTH(E5&1))*amount) where amount (C5:C16) and date (B5:B16) are named ranges. The result in F5 is 225, the sum of amounts in January for both 2022 and 2023. As the formula is copied down, we get a sum of amount by month that ignores years.

April 14, 2025 · 1 min · 47 words · Deborah Hood PhD

The solution below is based on the SUMPRODUCT function, the MONTH function, andBoolean algebra.

For convenience,amount(C5:C16) anddate(B5:B16) arenamed ranges.

All remaining values are FALSE, since other dates do not occur in January.

Excel formula: Sum by month