Calculate Total Weight from an Excel Dropdown and Quantity in WPS Spreadsheets
Question details
The user needs a formula that uses a data-validation dropdown to select an item, looks up its unit weight, and multiplies that weight by the quantity entered in the adjacent cell.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a custom calculator where A8 contains a data-validation dropdown, B8 contains the quantity, and the item-weight table is in G8:H1000.
- Observed behavior
- The total weight cell should return quantity multiplied by the selected item's unit weight and remain blank when the lookup would fail.
Make sure your item-weight table is placed in G8:H1000 or adjust the formula range to match your data. Item names should be in column G and unit weights in column H, and A8 should already contain a Data Validation dropdown.
Use IFERROR with VLOOKUP to Calculate Total Weight
Use this solution when you want a single fill-down formula that calculates each row's total weight and keeps the cell blank instead of showing lookup errors.
This formula reads the dropdown item in A8, finds its unit weight in the second column of the G8:H1000 table, and multiplies it by the quantity in B8. The IFERROR wrapper keeps the result clean when the item or quantity is not available.
Click C8, the first row where the calculated total weight should appear next to the dropdown and quantity cells.
Type =IFERROR(B8*VLOOKUP(A8,$G$8:$H$1000,2,FALSE),"") and press Enter. The formula looks up the item in A8 and multiplies its unit weight by B8.
Drag the fill handle from C8 down through C12 or the last row of your data so each row uses its own A and B cell references.
Select an item from the A8 dropdown and enter a quantity in B8. For example, two Bath Blankets at 660 grams each should return 1,320 grams.
Calculate Dropdown-Based Total Weight in WPS Office Spreadsheets
Use WPS Spreadsheets to build a dropdown calculator with VLOOKUP and IFERROR so selected item weights are multiplied by quantities automatically without showing errors.
- 1. Create the item-weight table: In WPS Spreadsheets, list item names in G8:G1000 and their unit weights in H8:H1000.
- 2. Set up the dropdown: Select A8, click Data > Validation, choose List, and set the source to your item name range.
- 3. Enter quantity cells: Type the quantity for each row in column B, such as the number of Bath Blankets.
- 4. Apply the total formula: In C8, enter =IFERROR(B8*VLOOKUP(A8,$G$8:$H$1000,2,FALSE),"") and fill it down to the last data row.
- 5. Check the results: Choose different items from the dropdown and confirm the calculated total weight updates correctly in column C.
Frequently Asked Questions
Can I use a different lookup table range?
Yes. Replace $G$8:$H$1000 with your actual table range. Keep the item names in the first column and the unit weights in the second column, or change the column index from 2 to match your table.
Why does the formula return a blank cell instead of a value?
The IFERROR wrapper returns an empty string when VLOOKUP cannot find the dropdown item or when required cells are blank. Check that the selected item exactly matches a value in the unit weight table.
What should I do if my item list is on another sheet?
Use a sheet reference in the lookup range, such as =IFERROR(B8*VLOOKUP(A8,Sheet2!$G$8:$H$1000,2,FALSE),"").
Does this formula work when I open the file in Microsoft Excel?
Yes. The formula uses standard Excel functions and will work in both WPS Spreadsheets and Microsoft Excel with .xlsx files.
How do I create the item dropdown in WPS Spreadsheets?
Select the cell, go to Data > Validation, choose List, and then enter the item range or type comma-separated item names as the source.
