Use SUMIF to Calculate Order Totals from an Excel Order Form in WPS Office
Question details
Calculate how much each player owes by summing item prices associated with selected products on an order form.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has an order form where product selections are in one row and matching prices are in another row. The goal is to total each player's selected items automatically.
- Observed behavior
- The desired behavior is a formula that sums the corresponding prices for all nonblank product selection cells so each player's total owed is displayed automatically.
Make sure your order form uses one row for product selections and a matching price range with the same number of columns. Check that all prices are numeric before entering the formula.
Use SUMIF to Add Prices for All Selected Items
Best when each selected item should be counted once and no quantity row is involved.
The formula checks the selection row for any nonblank cell and adds the corresponding price from the locked price row. Because the price range uses absolute references, you can copy the formula down for multiple players.
Click the cell where the player's order total should appear.
Type =SUMIF(B2:M2,"<>",$B$1:$M$1) and replace the ranges with your actual selection and price ranges.
Press Enter to calculate the total of all prices that match nonblank order cells.
Use SUMPRODUCT for Order Forms with Quantities
Use this when the order form includes a quantity row and each selected item needs its price multiplied by the quantity.
Create an Automatic Order Total with SUMIF in WPS Office Spreadsheets
Open your order form in WPS Office Spreadsheets and use SUMIF or SUMPRODUCT to calculate each player's total without manually adding selected prices.
- 1. Open your order form: Launch WPS Office and open the Excel or .xlsx order form in WPS Spreadsheets.
- 2. Set up the price range: Make sure the price row, for example B1:M1, is numeric and aligned with the product selection row.
- 3. Enter the SUMIF formula: Click the first player's total cell and type =SUMIF(B2:M2,"<>",$B$1:$M$1).
- 4. Fill down for other players: Drag the fill handle down to apply the formula to additional player rows.
- 5. Save your workbook: Press Ctrl+S and save the file in .xlsx format to keep the formulas.
Frequently Asked Questions
What does the SUMIF criteria "<>" mean in the order total formula?
It means "not blank." The formula checks each cell in the selection range and sums the price only when the corresponding selection cell is not empty.
How do I modify the formula if my order form has a quantity row?
Use SUMPRODUCT instead, for example =SUMPRODUCT(--(B2:M2<>""),B3:M3,$B$1:$M$1), so each selected quantity is multiplied by its matching price.
Can I use this SUMIF order total formula in both WPS Office and Microsoft Excel?
Yes. The formula uses standard spreadsheet syntax and works in WPS Office Spreadsheets and Excel with the same ranges and absolute references.
Why is my SUMIF formula returning 0 even though cells are selected?
Check that the price row is numeric and that the selection and price ranges are aligned column-for-column. If the price range is locked with $ but the selection range is wrong, the formula may reference empty cells and return 0.
