How to Build a Delivery Cost Formula from Multiple Conditions in WPS Spreadsheets
Question details
The user needs to build a spreadsheet delivery cost calculator that applies different rates based on delivery type, people, zone, and pallets.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user wants a formula that returns the correct delivery charge from multiple pricing conditions and can update when inputs change.
- Observed behavior
- The expected result is a formula that retrieves each applicable rate from a pricing table and totals the correct delivery cost.
Gather the complete delivery pricing rules: delivery types, zones, included people, extra-person hourly charges, pallet limits, and zone pallet prices. Open WPS Spreadsheets and set up one Rates sheet for the pricing table and one Delivery Calculator sheet for the formula inputs.
Create a Rate Table and Retrieve Charges with XLOOKUP
Use this when each delivery type and zone combination has one rate row and you need to pull base, extra-people, and pallet charges into a calculator.
A structured rate table keeps pricing rules visible and easy to update. In WPS Spreadsheets, XLOOKUP can match multiple criteria by multiplying Boolean arrays and return the matching rate for exact lookups.
Open WPS Spreadsheets and rename Sheet1 to Rates. Enter columns: Delivery Type, Zone, Included People, Extra Person Hourly Rate, Pallet Limit, Zone Price per Pallet, and Base Charge. Fill one row for every valid delivery type and zone combination.
Rename Sheet2 to Delivery Calculator. Add input cells for Delivery Type, Zone, People, and Pallets, then label output cells for Base Charge, Extra People Charge, Pallet Charge, and Total Delivery Cost.
In the Base Charge cell, enter =XLOOKUP(1,(B2=Rates!$A$2:$A$100)*(B3=Rates!$B$2:$B$100),Rates!$G$2:$G$100,"Check rates") and press Enter. Adjust the cell references to match your calculator inputs and rate table.
For Extra People Charge, use =MAX(0,B4-XLOOKUP(1,(B2=Rates!$A$2:$A$100)*(B3=Rates!$B$2:$B$100),Rates!$C$2:$C$100,0))*XLOOKUP(1,(B2=Rates!$A$2:$A$100)*(B3=Rates!$B$2:$B$100),Rates!$D$2:$D$100,0). For Pallet Charge, multiply the pallet input by the matched zone price per pallet.
In the Total Delivery Cost cell, enter =SUM(BaseCharge,ExtraPeopleCharge,PalletCharge) or reference the individual output cells. Test with known delivery scenarios to confirm the formula returns the expected amount.
Use SUMIFS When Multiple Rows Can Contribute to One Delivery Cost
Use this if the pricing model is stored as multiple rows per delivery and you need to sum charges such as zone fees, pallet fees, or service add-ons.
Validate the Delivery Cost Formula with a Small Sample Workbook
Use this before sharing the workbook to catch pricing logic errors and verify that the formula matches the business rates.
Build a Multi-Condition Delivery Cost Calculator in WPS Spreadsheets
WPS Spreadsheets can directly implement this delivery cost model. Use a rate table with XLOOKUP or SUMIFS to retrieve delivery type, zone, people, and pallet charges, then total them in a calculator sheet.
- 1. Install and open WPS Office: Download and install WPS Office, then open WPS Spreadsheets and create a new workbook or open your existing .xlsx delivery pricing file.
- 2. Build the rate table: Create a Rates sheet with columns for delivery type, zone, included people, extra-person hourly rate, pallet limit, zone price per pallet, and base charge.
- 3. Enter lookup formulas: Use XLOOKUP or SUMIFS from the Formulas tab to match the calculator inputs to the rate table, then add cells for base, extra people, and pallet charges.
- 4. Total and test the delivery cost: Sum the charge cells in a Total Delivery Cost field, then test several delivery type, zone, people, and pallet combinations against your expected results.
Frequently Asked Questions
Does WPS Spreadsheets support XLOOKUP for multiple delivery conditions?
Yes. In WPS Spreadsheets, click the cell where you need the rate, go to Formulas > Lookup & Reference > XLOOKUP, or type =XLOOKUP(...). Use multiple conditions by multiplying Boolean arrays, such as (DeliveryTypeRange=Type)*(ZoneRange=Zone).
What is the best way to organize delivery rates for zones, pallets, and people?
Put each pricing dimension in its own column in a Rates sheet and create one row per valid delivery type and zone combination. Include columns for included people, extra-person hourly rate, pallet limit, and zone price per pallet so each charge can be retrieved separately.
Why does my delivery cost formula return #N/A?
The combination is probably missing from the rate table or the lookup ranges do not match. Wrap the XLOOKUP with IFERROR, for example =IFERROR(XLOOKUP(...),"Missing rate"), and verify the Delivery Type and Zone values match exactly, including spaces and capitalization.
Should I use XLOOKUP or SUMIFS for a delivery cost model?
Use XLOOKUP when you need to retrieve one rate for a unique delivery type and zone. Use SUMIFS when multiple rows can match the same delivery and you need to total charges, such as base delivery, per-pallet zone fees, and add-on services.
