FORMULAS & FUNCTIONS
How to Sum Monthly Values with SUMIFS in Excel
Use SUMIFS to total one month of Excel data with reliable date criteria, then copy the formula across a monthly summary without missing records.
SUMIFS adds numbers only when all of the conditions you give Excel are true. For a monthly total, the safest idea is simple: include transactions on or after the first day of the month, and stop before the first day of the next month. That approach also handles transactions that contain a time as well as a date.
Build the SUMIFS monthly totals formula
Assume transaction dates are in A2:A1000, amounts are in C2:C1000, and the month to summarize starts in F2.
=SUMIFS($C$2:$C$1000,$A$2:$A$1000,">="&F2,$A$2:$A$1000,"<"&EDATE(F2,1))A second useful version is:
=SUMIFS(tblData[Amount],tblData[Date],">="&F2,tblData[Date],"<"&EDATE(F2,1))The first criterion includes dates on or after the first day of the month. The second criterion uses the first day of the next month as an exclusive upper boundary, so every time value on the final day is included.
Step-by-step formula workflow
- Store a real first-of-month date in the summary header.
- Use the amount column as the sum range.
- Add a greater-than-or-equal start-date criterion.
- Add a less-than first-day-of-next-month criterion.
- Copy the formula across after confirming the summary headers are real dates.
Common formula mistakes
| Mistake | Why it matters |
|---|---|
| Using month names as text criteria | Text labels do not reliably identify the year or the full date range. |
| Using less than or equal to the final day | Date-time values late on the final day can be missed. |
| Mismatched range sizes | Every SUMIFS criteria range must align with the sum range. |
| Dates stored as text | Text dates will not satisfy normal date criteria. |
| Hard-coding every month | A date header and EDATE make the formula reusable. |
Worked monthly example
Put 1-Jan-2026 in F2 as a real Excel date and format it as mmm yyyy. The formula then includes every transaction from January 1 up to, but not including, February 1. That boundary is safer than testing <=31-Jan-2026 because transaction data can contain times such as 31-Jan-2026 4:30 PM.
To total only one category as well, extend the Table version with another pair of criteria: =SUMIFS(tblData[Amount],tblData[Date],">="&F2,tblData[Date],"<"&EDATE(F2,1),tblData[Category],$B$1). Now B1 can hold a selected department or account. Before copying the summary across twelve months, confirm each header is a genuine first-of-month date rather than typed text such as Jan or February.
Frequently asked questions
Why use the first day of the next month?
It includes every date and time within the selected month without calculating the final day separately.
Can I add a category condition?
Yes. Add another criteria range and criterion for department, account, product, or another field.
Does SUMIFS work with an Excel Table?
Yes. Structured references expand automatically and are easier to audit.
Why is the result zero?
Check that the transaction dates are real Excel dates and that all ranges cover the same rows.
Continue building reliable Excel workflows
Browse more lessons in the OneXcel Tutorial Hub or explore free Excel resources.