How to Automatically Calculate Project Budget Totals by Category in WPS Spreadsheets
Question details
The user needs a formula-based method to automatically sum project expenditures by category as new rows are added to an Excel/WPS Spreadsheets budget worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing a project budget with expense amounts and category labels such as Planned Miscellaneous and Unplanned Miscellaneous.
- Observed behavior
- Category totals should update automatically when new expenditures are entered, without manually editing each total.
Open your budget workbook in WPS Spreadsheets and confirm that expense amounts are in one column and category labels are in an adjacent column, with the same category spelling used consistently. Also identify the summary range where the category list is located.
Use SUMIF to Total Project Budget Amounts by Category
Best when you have a simple range and want a quick formula that updates automatically as new amounts are added within the referenced range.
The SUMIF function sums the Amount column only when the Category column matches the category label in the summary row. The absolute references keep the data range locked while the category cell moves down.
Open the workbook in WPS Spreadsheets and click cell B2, or the first empty cell next to your category list, where the first category total should appear.
Type =SUMIF($E$20:$E$10000,A2,$D$20:$D$10000) and press Enter. Replace D20:D10000 with the actual Amount range and E20:E10000 with the actual Category range.
Drag the fill handle from B2 down through the remaining category rows so each summary row calculates its own total.
Add a test expenditure row within the referenced range and confirm the matching category total increases automatically.
Convert Data to an Excel Table for a Self-Expanding Category Total
Use this if you will keep adding rows beyond a fixed range and want the totals to include new rows automatically without updating formula ranges.
Create Automatic Project Budget Category Totals with WPS Spreadsheets
Use WPS Spreadsheets to apply a SUMIF formula or Excel Table structured reference so project budget totals for categories such as Planned Miscellaneous and Unplanned Miscellaneous update automatically whenever new expenditures are added.
- 1. Open your budget workbook: Launch WPS Spreadsheets and open the project budget workbook that contains the expenditure Amount and Category columns.
- 2. Prepare the category summary list: List unique categories such as Planned Miscellaneous and Unplanned Miscellaneous in a summary area, starting at A2.
- 3. Enter a SUMIF formula: Click the first total cell and enter =SUMIF($E$20:$E$10000,A2,$D$20:$D$10000), replacing the ranges with your data.
- 4. Fill down and test: Drag the fill handle down the summary list, then add a new expenditure row to verify the category total updates.
Frequently Asked Questions
Why is my SUMIF category total showing 0?
Check that the category label in the SUMIF criteria cell exactly matches the entries in the Category column, including extra spaces, spelling, and capitalization. SUMIF is not case-sensitive, but any accidental spaces will prevent a match.
How do the category totals update when I add new expenses?
As long as the new expenditure row is inside the referenced SUMIF range, WPS Spreadsheets recalculates the formula automatically. If you will add rows beyond that range, convert the data to an Excel Table so the structured reference expands automatically.
Can I use this SUMIF method in WPS Spreadsheets with an existing Excel file?
Yes. WPS Spreadsheets opens and saves Excel .xlsx files and supports SUMIF, Excel Tables, and structured references, so you can use the same workbook without losing compatibility.
How do I convert my budget data to an Excel Table in WPS Spreadsheets?
Click anywhere in the data range, go to Insert > Table in the ribbon, confirm the range and header checkbox, then click OK. You can then use table column names in the SUMIF formula.
