WPS Spreadsheets: Sum Values When a Related Cell Matches a Cost Code
Question details
The user needs to total values associated with a specified cost code.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Cost codes may repeat, appear in a different order, or be absent, and the user wants to summarize their corresponding values.
- Observed behavior
- The required total must be calculated by matching a cost code in one range and adding values from a related range.
Identify the cell containing the cost code to summarize, such as A22. Confirm that the criteria range and sum range contain the same number of corresponding rows and that both ranges use the intended source data.
Use SUMIF to total values for a matching cost code
Use this method when one range contains cost codes and another aligned range contains the values to add.
SUMIF checks each cell in the criteria range against the selected cost code, then adds the corresponding cells from the sum range. Repeated codes are included automatically, while codes with no match return 0.
In WPS Spreadsheets, click the cell where you want to display the total for the cost code in A22.
Type =SUMIF($B$4:$B$19,A22,$B$2:$B$17) in the selected cell, then press Enter.
Drag the fill handle or copy and paste the formula into the cells for the other cost codes. The absolute ranges remain fixed while the cost-code reference changes by row.
Verify that $B$4:$B$19 and $B$2:$B$17 have matching dimensions and that each row in the criteria range corresponds to the correct value in the sum range.
Calculate Cost-Code Totals Easily with WPS Spreadsheets
WPS Spreadsheets lets you use SUMIF to match cost codes and total related values in a familiar spreadsheet interface. Enter the formula, copy it for additional codes, and work with Microsoft Excel files without changing your workflow.
- 1. Open a spreadsheet: Launch WPS Office and open an existing spreadsheet or create a new workbook in WPS Spreadsheets.
- 2. Prepare the source data: Place cost codes in one range and their related values in an aligned range, then enter the target cost code in a summary cell such as A22.
- 3. Enter the formula: Click the result cell and enter =SUMIF($B$4:$B$19,A22,$B$2:$B$17), then press Enter.
- 4. Fill the summary: Copy the formula down or across for the remaining cost codes and confirm that the source ranges stay aligned.
Frequently Asked Questions
What does the SUMIF formula do in WPS Spreadsheets?
SUMIF checks a criteria range for a matching value and adds the corresponding cells in a sum range. In this example, it matches the cost code in A22 and totals the related values.
Can SUMIF handle duplicate cost codes?
Yes. SUMIF adds every value whose criteria-range cell matches the selected cost code, so repeated cost codes are included in the total.
Why must the criteria range and sum range be aligned?
The ranges should have the same number of corresponding rows so each matched cost code points to the correct value. Misaligned ranges can produce incorrect totals.
How do I summarize another cost code?
Copy the formula to another summary cell and update the criteria reference, or copy it down so A22 changes to the next cost-code cell while the dollar-sign ranges remain fixed.
