Sum Excel Mileage and Supply Values by Month with SUMIFS
Question details
Calculate monthly totals for mileage and supply values using dates stored in column C.
- Product
- Microsoft Excel spreadsheet formulas
- Device & OS
- not provided
- Scenario
- The user needs to summarize one or more numeric columns for a selected month represented by dates in column C.
- Observed behavior
- SUMIFS cannot use a transformed month-name expression such as TEXT(C2:C200,"mmmm") directly as its criteria range; monthly totals need date-boundary criteria or SUMPRODUCT.
Make sure the cells in column C contain real Excel dates rather than text. For the SUMIFS method, enter the first day of the target month in H2, such as 1/1/2026.
Use SUMIFS with the first and next month dates
Use this recommended method when your date column contains valid dates and you want an efficient monthly total.
SUMIFS works with range references, so use the first day of the selected month as the lower boundary and the first day of the next month as the upper boundary. This includes every date in the target month without relying on month names.
In H2, enter the first day of the month to summarize, for example, 1/1/2026. Format H2 as a date or month-year as needed.
In I2, enter =EDATE(H2,1) to return the first day of the following month.
Select the monthly mileage total cell and enter =SUMIFS(F2:F200,C2:C200,">="&H2,C2:C200,"<"&I2). Replace F2:F200 with your mileage-value range if it differs.
In the supply total cell, use the same date criteria with the supply range, such as =SUMIFS(G2:G200,C2:C200,">="&H2,C2:C200,"<"&I2).
Use SUMPRODUCT when matching a displayed month name
Use this method when H2 contains a month name, such as January, and you specifically want to compare month text derived from each date.
Calculate Monthly Totals with SUMIFS in WPS Spreadsheet
WPS Spreadsheet supports SUMIFS, SUMPRODUCT, EDATE, and date-based criteria, so you can total mileage, supplies, or other values for a selected month using the same familiar spreadsheet workflow.
- 1. Open the worksheet in WPS Spreadsheet: Open the Excel workbook in WPS Office and select the sheet containing the dates in column C and the value columns.
- 2. Set the reporting month: Enter the first day of the target month in H2, then enter =EDATE(H2,1) in I2.
- 3. Add the monthly total formula: Select the result cell and enter =SUMIFS(F2:F200,C2:C200,">="&H2,C2:C200,"<"&I2).
- 4. Reuse the date criteria for other values: Copy the formula to another result cell and change F2:F200 to the supply or other amount range.
Frequently Asked Questions
Why does SUMIFS not work with TEXT(C2:C200,"mmmm") as the criteria range?
SUMIFS requires an actual cell range for its sum range and criteria ranges. TEXT(C2:C200,"mmmm") creates a calculated array, so use date boundaries with SUMIFS or use SUMPRODUCT for the text comparison.
What formula totals values for the month shown in H2?
If H2 contains the first day of the target month and I2 contains =EDATE(H2,1), use =SUMIFS(F2:F200,C2:C200,">="&H2,C2:C200,"<"&I2).
How do I total a second column, such as supply costs, for the same month?
Keep the date criteria unchanged and replace the sum range. For example, use =SUMIFS(G2:G200,C2:C200,">="&H2,C2:C200,"<"&I2) when supply costs are in column G.
Will the SUMIFS date-boundary formula work when dates include times?
Yes. Using a greater-than-or-equal-to month start date and a less-than next-month start date includes all timestamps within the selected month.
How can I use SUMPRODUCT for a specific month and year?
Put the month name in H2 and the year in I2, then use =SUMPRODUCT(F2:F200*(TEXT(C2:C200,"mmmm")=H2)*(YEAR(C2:C200)=I2)).
