Apply Multiplication, Division, and Subtraction Instantly with XLOOKUP and SUMPRODUCT

The XLOOKUP SUMPRODUCT combination is a powerful pairing that even beginners can use right away at work. This article provides formulas for handling unit price × quantity (multiplication), currency conversion (division/multiplication), return deductions (subtraction), and weighted averages. Follow the Quick Fix section below to get totals immediately, then use the principles and practical examples that follow to build a solid understanding.

Quick Fix: 4 Ready-to-Use Formulas

1. Total Amount by Item (Unit Price × Quantity)

   =SUMPRODUCT( XLOOKUP( A2:A10, 품목표[품목], 품목표[단가] ) * B2:B10 )

   What it does: Looks up the unit price for each item in the order list (column A) from the item table, multiplies it by the quantity (column B), and adds everything together.

2. Total Currency Conversion (Foreign Currency Amount → KRW)

   =SUMPRODUCT( A2:A10 * XLOOKUP( 통화코드, 환율표[코드], 환율표[매매기준율] ) )

   What it does: Multiplies the foreign currency amounts in column A by the exchange rate for each currency to calculate the total in KRW. (For KRW → foreign currency, change * to /.)

3. Net Sales After Returns

   =SUMPRODUCT( XLOOKUP( 주문품목, 품목표[품목], 품목표[단가] ) * 주문수량 ) 
    - SUMPRODUCT( XLOOKUP( 반품품목, 품목표[품목], 품목표[단가] ) * 반품수량 )

   What it does: Subtracts the value of returned items from total sales to calculate net sales.

4. Weighted Average Unit Price (Total Amount ÷ Total Quantity)

   =SUMPRODUCT( XLOOKUP( A2:A10, 품목표[품목], 품목표[단가] ) * B2:B10 ) / SUM(B2:B10)

   What it does: Divides the total amount across all items by the total quantity to calculate the weighted average unit price.

※ These formulas work especially cleanly in current versions of Excel (or Microsoft 365) that support dynamic arrays. The table names and headers are examples, so adjust them for your own data ranges.

Why Combine XLOOKUP + SUMPRODUCT?

• XLOOKUP: Finds the exact value you need using a key such as an item or code, with options for exact or approximate matches, search direction, not-found handling, and more.
• SUMPRODUCT: Multiplies multiple arrays element by element (or combines multiplication, subtraction, and division) and applies conditions as weights to calculate a total in one step.
• Together, they let you calculate immediately using looked-up values, all in one cell without helper columns.

Sample Data (Follow Along)

1) Order Table (range A1:C10, table name: 주문)

2) Item Table (range E1:G6, table name: 품목표)

3) Exchange Rate Table (range I1:J4, table name: 환율표)

Four Essential Practical Patterns: Formulas and Principles

① Total Unit Price × Quantity (Foreign Currency Amount)

=SUMPRODUCT(
  XLOOKUP( 주문[품목], 품목표[품목], 품목표[단가(외화)] )
  * 주문[수량]
)

How it works: XLOOKUP returns an array of foreign currency unit prices for each ordered item → the prices are multiplied by the corresponding quantities element by element → SUMPRODUCT totals the results.

② Total KRW Conversion (Foreign Currency → KRW)

=SUMPRODUCT(
  XLOOKUP( 주문[품목], 품목표[품목], 품목표[단가(외화)] )
  * 주문[수량]
  * XLOOKUP( 주문[통화], 환율표[코드], 환율표[매매기준율] )
)

How it works: Multiplies (foreign currency unit price × quantity) by the exchange rate for each currency element by element to convert the amounts to KRW, then adds them together.

Reverse conversion: For KRW → foreign currency, change the final portion to / XLOOKUP(...환율...).

③ Net Sales After Returns

Assume a returns table (for example, K1:L5, “반품”) with the Item and Return Quantity columns.

=SUMPRODUCT( XLOOKUP( 주문[품목], 품목표[품목], 품목표[단가(외화)] ) * 주문[수량] )
 - SUMPRODUCT( XLOOKUP( 반품[품목], 품목표[품목], 품목표[단가(외화)] ) * 반품[반품수량] )

How it works: Subtracts the total value of returns (unit price × return quantity) from total sales. Apply exchange-rate multiplication or division in the same way if needed.

④ Weighted Average Unit Price

=SUMPRODUCT(
  XLOOKUP( 주문[품목], 품목표[품목], 품목표[단가(외화)] ) * 주문[수량]
) / SUM(주문[수량])

How it works: Total amount ÷ total quantity. For a KRW-based weighted average that includes exchange rates, also multiply the numerator by the exchange-rate term.

Multi-Criteria XLOOKUP: Brand + Size and More

For XLOOKUP, a reliable pattern is to multiply the conditions (Boolean arrays) to locate the row where all conditions are TRUE, or 1.

=XLOOKUP(
  1,
  (상품표[브랜드]=H2) * (상품표[사이즈]=H3),
  상품표[단가]
)

Use this returned array in SUMPRODUCT to extend multiplication, division, and subtraction to multi-criteria unit prices.

Common Mistakes & Checklist

• Header typos: Table headers must match exactly. Make structured references a habit (for example, 주문[수량]).
• Missing currency codes: A code missing from the exchange-rate table returns #N/A. Use XLOOKUP’s if_not_found argument to specify a default value.
• Data types: Mixing numbers and text can result in zeros or errors during multiplication. If needed, use -- to coerce values into numbers.
• Mismatched range lengths: All SUMPRODUCT arrays must have the same length.

Troubleshooting

Alternatives and Extensions: Combine with SUM, LET, and TAKE

• SUM vs. SUMPRODUCT: If you only need a simple total, SUM(XLOOKUP(...)*수량) also works with dynamic arrays, but SUMPRODUCT is more reliable for conditional weighting and combining multiple arrays.
• LET: Assign repeated calculations to variables to improve calculation speed and readability.
• Weighted average cost: Apply the same pattern to receipt tables containing quantity and cost.

Related Articles

• XLOOKUP Basics Guide
• Understanding SUMPRODUCT
• A Reliable Multi-Criteria XLOOKUP Pattern

External Sources (Authoritative Documentation)

• Microsoft Support — XLOOKUP function
• Microsoft Support — SUMPRODUCT function

Conclusion — Master these four XLOOKUP SUMPRODUCT patterns, and you can handle unit price × quantity, currency conversion, return deductions, and weighted averages in one cell without helper columns. Paste them directly into your workbooks and try them out. For a deeper dive, see the related articles above.