How to Calculate Totals by Cost Center with SUMIF in WPS Office Spreadsheets
Question details
The user needs to automatically calculate row and combined totals by cost center using SUMIF, with results that refresh when cost center values change.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a worksheet with cost center values and wants row and combined totals that update when a cost center changes.
- Observed behavior
- The desired state is for row and combined totals to recalculate automatically when a cost center or source value changes.
Prepare your worksheet so the source data has a cost center column and one or more value columns, and decide where the summary totals will be placed.
Use SUMIF to Calculate and Auto-Update Cost Center Totals
Use this solution when you want each cost center total to update automatically whenever the cost center name or source values change.
SUMIF adds cells in a range when a corresponding cell meets a condition. In WPS Office Spreadsheets, the same formula syntax works as in Excel, so you can use it in .xlsx workbooks without conversion.
Open your workbook in WPS Office Spreadsheets and click the cell where the first cost center total should appear, such as B8.
Type =SUMIF($A$2:$A$5,$A8,B$2:B$5) in the formula bar or directly in the cell, replacing the ranges with your actual data range.
Press Enter to calculate the first total. Drag the fill handle from the bottom-right corner of the cell across the row and down the column to copy the formula.
Change a cost center name or source value in the detail area, and check that the summary totals recalculate automatically.
Calculate Cost Center Totals with SUMIF in WPS Office Spreadsheets
Use WPS Office Spreadsheets to build automatic cost center summaries with SUMIF. Enter the formula once, fill across and down, and totals update whenever source data changes.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the workbook containing cost center data, or click Menu > New to create a new spreadsheet.
- 2. Enter the SUMIF formula: Click the summary cell and type =SUMIF($A$2:$A$5,$A8,B$2:B$5), using your actual cost center column and value columns.
- 3. Fill the formula across and down: Press Enter, then drag the fill handle across the row and down the summary area to apply SUMIF to all cost centers and columns.
- 4. Check the totals: Change a cost center or amount in the source range and confirm the summary totals update automatically.
Frequently Asked Questions
Will the totals update automatically if I change a cost center or amount?
Yes. Because the formula uses SUMIF with cell references, WPS Office Spreadsheets recalculates the totals automatically when the cost center name or source values change.
How do I apply the same SUMIF formula to multiple rows and columns?
Use a formula like =SUMIF($A$2:$A$5,$A8,B$2:B$5), press Enter, and drag the fill handle across the row and down the summary area. The absolute cost center range keeps the criteria column fixed while the sum range adjusts.
Can I total values based on more than one condition?
Yes. If you need multiple conditions, use SUMIFS instead. In WPS Office Spreadsheets, start typing =SUMIFS( in the target cell and follow the function argument prompts.
Does this SUMIF method work with existing Excel workbooks?
Yes. WPS Office Spreadsheets is compatible with Microsoft Excel .xlsx files, so you can open an existing workbook, enter the same SUMIF formula, and save it without converting the file format.
