How to Use SUMIFS to Total Expenses by Month and Category in Excel
Question details
The user wants to calculate total expenses based on specific months and categories using the SUMIFS function instead of combining IF and AND functions.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Summarizing financial data or expense reports by date range and expense category.
- Observed behavior
- The user needs a clean formula to sum amounts matching multiple criteria such as a specific month and a specific text category.
Ensure your expense data is organized in clear, consistent columns (e.g., Dates, Categories, and Amounts) and verify that your date column is formatted as actual date values, not plain text.
Using the SUMIFS Function for Multiple Criteria
Apply the SUMIFS formula to sum data matching a specific date range and text category without needing helper columns.
The SUMIFS function is designed to add all its arguments that meet multiple criteria. To sum by a specific month, you define the start and end dates using the DATE function combined with logical operators.
Identify the columns containing your amounts (e.g., Column D), dates (e.g., Column A), and categories (e.g., Column B).
Click the empty cell where you want the final expense total to appear.
Type the formula exactly as follows: =SUMIFS(Expenses!D2:D1000, Expenses!A2:A1000, ">"&DATE(2024,3,31), Expenses!A2:A1000, "<"&DATE(2024,5,1), Expenses!B2:B1000, "Web Advertising")
Modify the ranges (like D2:D1000), adjust the DATE functions to bracket your desired month (e.g., after March 31 and before May 1 for April), change the category string to your target category, and press Enter.
Calculate Complex Totals Easily with WPS Spreadsheet
WPS Spreadsheet fully supports SUMIFS and hundreds of other advanced formulas, making expense tracking and detailed data analysis incredibly easy.
- 1. Open your expense sheet: Launch WPS Spreadsheet and open your .xlsx expense report document.
- 2. Use the Insert Function tool: Navigate to the 'Formulas' tab on the top ribbon and click 'Insert Function'. Search for 'SUMIFS' in the prompt box.
- 3. Fill in the arguments seamlessly: Follow the dialog box prompts to easily select your sum range, criteria ranges, and date/category criteria without having to memorize the exact syntax and quotation marks.
Frequently Asked Questions
Can I use cell references for the date criteria in SUMIFS instead of the DATE function?
Yes. If you have the start date in cell E1 and the end date in E2, you can write the criteria as ">="&E1 and "<="&E2. This makes your formula dynamic and much easier to update without editing the formula text.
Why is my SUMIFS formula returning a zero?
A zero usually indicates that no cells met all the required criteria. Check if your date column is actually formatted as dates rather than text, ensure there are no trailing spaces in your category names, and verify that the date logic brackets a valid range.
What is the difference between SUMIF and SUMIFS?
SUMIF allows only one condition (e.g., total expenses only for 'Web Advertising'). SUMIFS allows multiple conditions (e.g., total expenses for 'Web Advertising' AND within a specific month). Additionally, in SUMIFS, the range to be summed is the first argument, whereas in SUMIF, it is the last.
