Use SUMIFS and SUMPRODUCT to Build an Excel Summary Report in WPS Office
Question details
The user needs to aggregate actual, budget, and prior-year values on a Summary sheet in WPS Office Spreadsheets by using SUMIF, SUMIFS, and SUMPRODUCT formulas, with criteria for account or category, period, and an actual/budget selector.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a summary report that pulls actual, budget, and prior-year data into one sheet where a selector can switch between actual and budget worksheets.
- Observed behavior
- The formulas should return the correct aggregated values by account or category and period, and update when the selector changes between A and B.
Open the workbook containing the data worksheets and the Summary sheet. Confirm that the actual, budget, and prior-year sheets use consistent columns for account or category, period, and values—especially columns A, D, and E referenced in the formulas.
Build the actual/budget selector formula with IF and SUMIFS
Use this solution when the Summary sheet has a selector cell such as F2 that switches between A for actuals and B for budget.
This formula nests two SUMIFS functions inside IF. It checks the selector cell F$2 and returns the actual or budget total for the matching account and period; if the selector does not match, it returns an empty string.
Click the first cell where the actual or budget value should appear, such as Summary!F4.
Type =IF(F$2="A",SUMIFS('2023 Actuals'!$E:$E,'2023 Actuals'!$D:$D,F$3,'2023 Actuals'!$A:$A,$A4),IF(F$2="B",SUMIFS('2023 Budget'!$E:$E,'2023 Budget'!$D:$D,F$3,'2023 Budget'!$A:$A,$A4),"")) and press Enter.
Drag the fill handle across the period columns and down the account rows. The mixed references F$3, $A4, and F$2 adjust correctly as you copy.
Return prior-year data with a dedicated SUMIFS formula
Use this for the prior-year column when the data PY sheet stores the period in a separate column instead of the period column used by actual and budget sheets.
Use SUMPRODUCT for multi-condition totals without changing worksheet columns
Use this solution when you need a single formula to test multiple conditions across finite ranges, or when you prefer an alternative to SUMIFS.
Build Your Summary Report with WPS Office Spreadsheets
WPS Office Spreadsheets supports SUMIFS, SUMIF, and SUMPRODUCT, so you can aggregate actual, budget, and prior-year data across sheets and keep the report updated without changing formula logic.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the actual, budget, prior-year, and Summary sheets.
- 2. Select the Summary cell: Click the first cell where the aggregated value should appear, such as F4 on the Summary sheet.
- 3. Enter the SUMIFS formula: Type the SUMIFS formula that references the actual or budget sheet, period cell, and account cell exactly as described in the recommended solution.
- 4. Fill the formula across the report: Drag the fill handle across period columns and down account rows to populate the full summary report.
- 5. Test the selector: Change the selector cell between A and B, or update a prior-year source value, and confirm that the report recalculates instantly.
Frequently Asked Questions
Why does my SUMIFS formula return #VALUE! when referencing another sheet?
This usually happens when a sheet name contains spaces or special characters, or when the criteria ranges are not the same size as the sum range. Wrap sheet names in single quotes, such as '2023 Actuals'!$E:$E, and make sure every criteria_range matches the row count of the sum_range.
Can I switch between actual and budget without editing every formula?
Yes. Use a selector cell such as F2 with A or B, and wrap two SUMIFS formulas in IF functions: =IF(F$2="A", actual_formula, IF(F$2="B", budget_formula, "")). Fill the formula across and down to apply the same switch to the whole report.
How do I write the prior-year formula if the period is in a separate column?
Use =SUMIFS('data PY'!$E:$E,'data PY'!$A:$A,Summary!$A4,'data PY'!$B:$B,Summary!$B4). Replace the 'data PY'!$B:$B range and Summary!$B4 criteria with the correct prior-year period column and cell.
When should I use SUMPRODUCT instead of SUMIFS?
Use SUMPRODUCT when you need to evaluate multiple conditions across finite ranges, perform calculations inside the condition, or when you prefer a single array-style formula. Keep ranges finite to avoid performance issues.
How can I use period headers like Jan-2023 correctly in the formula?
Enter a real date such as 1/1/2023 in the period header and format it as mmm-yyyy, then use the same header cell as the SUMIFS criteria. If the header is text, use CONCATENATE(LEFT(F$3,4),"/",RIGHT(F$3,3)) to match the source period format.
