How to Calculate Monthly Invoice Totals with SUMIFS in Excel
Question details
Calculate monthly totals for invoices and outstanding amounts across different supplier worksheets using date ranges.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Generating a monthly summary report from multiple supplier worksheets containing varied invoice dates and amounts.
- Observed behavior
- Requires a formula to correctly identify and sum invoice amounts that fall strictly within specific calendar months.
Ensure that all your invoice dates in the worksheet and the summary month-start dates are formatted as valid Excel dates, otherwise the SUMIFS date criteria will fail to calculate correctly.
Use SUMIFS and EOMONTH to Calculate Monthly Totals
This method uses the SUMIFS function coupled with EOMONTH to automatically sum values between the first and last day of a specific month.
The SUMIFS function allows you to sum values based on multiple criteria. By setting the criteria to be greater than or equal to the first day of the month, and less than or equal to the last day of the month, you can accurately capture all invoices for that period.
In your summary sheet, enter the start date of the target month in a reference cell (for example, enter 01/01/2024 in cell O1).
Select the cell for your monthly total and type the formula: =SUMIFS('A2 Tyres'!$F$3:$F$200,'A2 Tyres'!$A$3:$A$200,">="&O$1,'A2 Tyres'!$A$3:$A$200,"<="&EOMONTH(O$1,0))
Replace 'A2 Tyres'!$F$3:$F$200 with your invoice amount column, and 'A2 Tyres'!$A$3:$A$200 with your invoice date column. Press Enter to calculate the total.
Drag the formula across your summary table to apply it to other months or suppliers. The absolute references ($) ensure your lookup ranges remain fixed.
Group Dates in a Pivot Table for Monthly Summaries
If you have all your supplier data consolidated in a single master sheet, Pivot Tables offer a formula-free way to group and sum invoices by month.
Easily Manage Financial Data with WPS Spreadsheet
WPS Office provides powerful spreadsheet tools perfectly compatible with Excel formulas like SUMIFS and EOMONTH, making it easy to calculate monthly totals for your business invoices.
- 1. Open your file in WPS Spreadsheet: Download and launch WPS Office, open the Spreadsheet module, and load your existing invoice workbook.
- 2. Apply your SUMIFS formula: Use the exact same SUMIFS and EOMONTH formula syntax you would use in Excel to generate your monthly totals.
- 3. Save and share seamlessly: Save your completed summary report in .xlsx format to ensure seamless sharing with clients or colleagues who use Microsoft Office.
Frequently Asked Questions
Why is my SUMIFS formula returning zero for the monthly totals?
This usually happens if the dates in your worksheet are stored as text instead of valid date formats. Select your date columns, go to the Home tab, and change the number format to 'Short Date'. Also, ensure your criteria format includes the ampersand correctly, like ">="&O1.
Can I use SUMIFS across multiple sheets at once?
SUMIFS cannot directly calculate 3D references across multiple sheets in a single formula. You will need to write a separate SUMIFS for each sheet and add them together (e.g., =SUMIFS(Sheet1...) + SUMIFS(Sheet2...)) or consolidate your data into one sheet.
What does the '0' mean in the EOMONTH function?
The '0' in EOMONTH(O1, 0) tells the function to return the last day of the exact same month as the date in cell O1. Changing it to '1' would return the last day of the following month, and '-1' would return the last day of the previous month.
