How to Sum Values from Another Excel Sheet Using Multiple Criteria
Question details
The user needs to automatically calculate the total of supplier values from one worksheet and display it on another worksheet based on specific matching criteria.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Aggregating data across different sheets, such as totaling supplier costs or invoices when specific text criteria (like an N/C tag or ID) are met.
- Observed behavior
- A dynamic formula is required to pull and sum matched values from a source sheet into a destination sheet automatically without manual calculation.
Ensure both your source data sheet and destination sheet are in the same workbook. Verify that the ranges containing the criteria and the values to be summed are of the exact same size to prevent calculation errors.
Use the SUMIFS Function Across Worksheets
The SUMIFS function is the standard method for summing values based on one or more criteria, even when the data resides on a different sheet.
SUMIFS adds all of its arguments that meet multiple criteria. Its syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).
When referencing another sheet in your formula, you must include the sheet name followed by an exclamation mark (!) immediately before the cell range.
Open the worksheet where you want the total to appear (e.g., Sheet 2) and click on the target cell (e.g., C3).
Type the formula =SUMIFS('SHEET 1'!$D$3:$D$100, 'SHEET 1'!$C$3:$C$100, B3) into the formula bar. In this example, 'SHEET 1'!$D$3:$D$100 is the range of values to sum, 'SHEET 1'!$C$3:$C$100 is the criteria range to evaluate, and B3 contains the specific criterion you want to match on the current sheet.
Replace 'SHEET 1' with the exact name of your source data sheet. Make sure to use absolute references (the $ signs) so the range remains fixed and doesn't shift when you copy the formula.
Press Enter to apply the formula. To calculate totals for other criteria, click the small square at the bottom-right corner of the cell and drag it down to fill the formula into the remaining rows.
Calculate Data Across Sheets Effortlessly in WPS Office
WPS Spreadsheet fully supports SUMIFS and other advanced array formulas to help you process complex data across multiple worksheets. It provides an intuitive formula interface highly compatible with Microsoft Excel.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the multiple sheets.
- 2. Use the Insert Function tool: Navigate to the 'Formulas' tab and click on 'Insert Function'. Search for 'SUMIFS' in the dialog box and click OK.
- 3. Select cross-sheet ranges visually: In the Function Arguments dialog, click the range selector icon for 'Sum_range', switch to your source sheet, and highlight the values. Repeat this step for 'Criteria_range1'.
- 4. Apply criteria and fill: Select the cell containing your matching criteria, click OK to execute, and drag the fill handle down to apply the formula to adjacent cells.
Frequently Asked Questions
Why is my SUMIFS formula returning a #VALUE! error?
A #VALUE! error in SUMIFS usually occurs if the sum_range and the criteria_range arrays are not the exact same size. Double-check that both ranges start and end on the same row, for example, making sure both are set to rows 3 through 100.
Can I use SUMIFS to match text containing specific words?
Yes, you can use wildcard characters in your criteria. Use an asterisk (*) to match any sequence of characters or a question mark (?) to match a single character. For example, using "*supplier*" as your criterion will match any cell containing the word "supplier".
How do I add a second condition to my SUMIFS formula?
The SUMIFS function natively allows for multiple criteria. Simply add another criteria range followed by its corresponding criteria at the end of the formula: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2).
Why does my formula break when I copy it to the next row?
If you don't use absolute references (adding $ signs before the column letters and row numbers, like $D$3:$D$100), the range will automatically shift down as you copy the formula. Highlight the range in your formula bar and press F4 to lock it.
