How to Multiply Excel Values Based on a Type and Pricing Table
Question details
The user wants to calculate total costs by multiplying service quantities with specific rates retrieved from a separate pricing table based on project types.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating project totals using dynamic rates stored in a different sheet.
- Observed behavior
- The user needs a dynamic formula to automatically filter the correct rates by project type and multiply them by the corresponding quantities.
Ensure your pricing data is formatted as an official Excel Table (Ctrl+T) and named appropriately (e.g., 'Pricing'). This makes referencing ranges in dynamic formulas much easier and prevents errors when adding new data.
Use FILTER and SUM for Dynamic Pricing Calculation
Combine the FILTER and SUM functions to look up the correct rates based on the project type and multiply them by your service quantities.
This method uses dynamic array formulas to extract the exact row of rates from your pricing table that matches the specified project type. It then multiplies those rates by the quantities in your current table and sums the result.
Select your pricing data and press Ctrl+T to create a table. Name this table 'Pricing' in the Table Design tab. Do the same for your project quantities table, naming it 'Table1'.
In your total column, enter the formula: =IF(ISNUMBER([@Type]),SUM(FILTER(Pricing[[Service 1]:[Service 4]],Pricing[Type]=[@Type])*Table1[@[Service 1]:[Service 4]]),"")
Replace 'Pricing[[Service 1]:[Service 4]]' and 'Table1[@[Service 1]:[Service 4]]' with the actual column headers used in your specific spreadsheet.
Press Enter. The formula will automatically calculate the total by multiplying the filtered rates with the corresponding quantities.
Use XLOOKUP or VLOOKUP for Individual Service Calculations
If you are not using dynamic arrays or have a simpler table structure, you can look up each service rate individually and multiply it by the quantity.
Calculate Complex Pricing Tables Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas like FILTER, XLOOKUP, and SUM, making it incredibly easy to build automated pricing calculators and project estimators.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your pricing and project data files.
- 2. Format as Table: Highlight your pricing data, go to the Home tab, and select 'Format as Table'. Name it 'Pricing'.
- 3. Apply the formula: Type the FILTER and SUM formula into your totals column. WPS Spreadsheet will instantly calculate the cross-multiplied results.
Frequently Asked Questions
Why is my FILTER formula returning a #CALC! error?
The #CALC! error usually occurs when the FILTER function cannot find a match for the project type in your pricing table. You can fix this by adding an 'if_empty' argument to the FILTER function, like FILTER(range, criteria, "Not Found").
Can I use this formula in older versions of Excel?
The FILTER function is a dynamic array function available only in Microsoft 365, Excel 2021, and modern spreadsheet software like WPS Office. For older versions, you must use alternative functions like VLOOKUP or INDEX/MATCH combined with SUMPRODUCT.
How do I handle empty project type cells?
Wrap your main formula in an IF statement to check if the type cell is empty. For example, =IF([@Type]="", "", [Your Formula]) ensures the cell remains blank until a project type is entered.
Does the column order in both tables need to match exactly?
Yes, if you are multiplying the entire array (e.g., Service 1 to Service 4) from the Pricing table directly with the array in your project table, the columns must be in the exact same order for the math to align correctly.
