Correct a SUMIFS Formula for Monthly Resource Totals in WPS Spreadsheets
Question details
The user needs to correct a SUMIFS formula that totals amounts for each resource type and can also be expanded to total a resource type for a selected month.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a resource-total summary table with resource names or types listed in column N and wants the result displayed beginning in cell O7.
- Observed behavior
- The SUMIFS formula fails because the criteria range and criterion arguments are not paired correctly.
Check that the amount values are in D8:D41 and the corresponding resource types are in B8:B41. Make sure the value in N7 exactly matches the resource type text used in column B, including any spaces.
Use a paired criteria range and criterion for each resource type
Use this formula when column B contains resource types and column D contains the amounts to total.
SUMIFS requires each criteria range to be immediately followed by the criterion that applies to that range. The amount range and every criteria range must cover matching rows.
In WPS Spreadsheets, click cell O7, where the first resource total should appear.
Type =SUMIFS(D$8:D$41,$B$8:$B$41,$N7) and press Enter. This sums amounts in D8:D41 when the resource type in B8:B41 matches the value in N7.
Select O7 and drag the fill handle down to copy the formula to the remaining summary rows. The reference $N7 changes to N8, N9, and so on, while the source ranges remain fixed.
Add a second SUMIFS criterion to calculate a resource total by month
Use this method when the source table has a separate month or date column and the total must match both a resource type and a selected month.
Calculate Resource and Monthly Totals with SUMIFS in WPS Spreadsheets
WPS Spreadsheets lets you build multi-criteria totals with SUMIFS using a familiar worksheet interface. Enter the amount range, pair every criteria range with its matching criterion cell, and fill the formula through your resource summary.
- 1. Open the worksheet in WPS Spreadsheets: Open your resource data file and select the worksheet containing the source table and summary area.
- 2. Select the first summary result cell: Click the first total cell, such as O7, beside the resource criterion in N7.
- 3. Enter the resource total formula: Enter =SUMIFS(D$8:D$41,$B$8:$B$41,$N7), then press Enter to calculate the matching amount total.
- 4. Add a month condition if required: Append a month criteria range and month criterion cell to the SUMIFS formula when your source data includes a separate month field.
- 5. Fill the formula through the summary: Drag the fill handle across or down and verify that the relative resource and month references move as intended.
Frequently Asked Questions
Why does my SUMIFS formula return an error or incorrect total?
Check that every criteria range is followed by its matching criterion, such as $B$8:$B$41,$N7. Also ensure the sum range and all criteria ranges begin and end on the same rows.
What formula totals the amounts for the resource type in N7?
In O7, enter =SUMIFS(D$8:D$41,$B$8:$B$41,$N7). It totals values in column D where column B matches the resource type in N7.
How can I include a month in the SUMIFS calculation?
Add another criteria pair after the resource criterion. For example, if months are in C8:C41 and the target month is in O6, use =SUMIFS(D$8:D$41,$B$8:$B$41,$N7,$C$8:$C$41,O$6).
Should I use dollar signs in a SUMIFS formula before filling it?
Yes. Use absolute references such as $B$8:$B$41 for fixed data ranges. Use mixed references such as $N7 or O$6 when the criterion should change only down rows or only across columns.
Can WPS Spreadsheets open an Excel workbook with SUMIFS formulas?
Yes. WPS Spreadsheets supports Microsoft Excel .xlsx and .xls workbooks and can calculate standard formulas such as SUMIFS.
