SUMIFS Over a Date Range: Patterns That Do Not Break

SUMIFS is the default way to sum amounts between two dates. The usual bug is comparing a real date column to the text 1/1/2024, which Excel may not coerce the way you expect.

Always build dates with DATE or cells

Use ">="&DATE(2024,1,1) or ">="&$E$1 where E1 is a date-formatted cell. Concatenating a string date is locale-fragile. Inclusive ranges need both >= start and <= end.

Current month without rewriting

EOMONTH(TODAY(),-1)+1 is the first day of this month; EOMONTH(TODAY(),0) is the last. Pair them in SUMIFS so a dashboard does not need a new formula on the 1st.

When SUMIFS is not enough

• Criteria on a formula result (MONTH(A2)=3) — use SUMPRODUCT or a helper column.
• OR across products — add two SUMPRODUCT tests or two SUMIFS.
• Need a unique count in the window — UNIQUE/FILTER on 365, SUMPRODUCT on 2016.