Introduction
The SUMPRODUCT function in Excel multiplies corresponding values in two or more arrays and then adds the results. It is useful for calculating total sales, inventory value, employee payments, weighted averages, and totals based on multiple conditions.
In this tutorial, you will learn how to use SUMPRODUCT Function in Excel Tutorial with practical examples and company data. You will also learn how to calculate results without creating extra columns in your worksheet.
1. What Is the SUMPRODUCT Function in Excel?
The SUMPRODUCT function multiplies matching values from different ranges and adds their products.
Syntax:
=SUMPRODUCT(array1, [array2], ...)
- array1: The first range of values.
- array2: The second range of values to multiply by the first range.
- Additional arrays: Optional ranges that can be included in the calculation.
For example, if you sell 10 products for ₹500 each and 5 products for ₹800 each, SUMPRODUCT can calculate the total revenue directly.
Question 1: Calculate Total Sales Without a Helper Column
A company wants to calculate total sales from product quantities and selling prices. Instead of multiplying each row in a separate column, use SUMPRODUCT.
Data:
| Product | Quantity | Price per Unit |
|---|---|---|
| Keyboard | 10 | ₹500 |
| Mouse | 15 | ₹300 |
| Monitor | 8 | ₹1,200 |
| Printer | 12 | ₹750 |
Excel Formula:
=SUMPRODUCT(B2:B5,C2:C5)
Expected Result:
₹28,100
Explanation:
Excel multiplies the quantity by the price for each product and adds the results:
- Keyboard: 10 × ₹500 = ₹5,000
- Mouse: 15 × ₹300 = ₹4,500
- Monitor: 8 × ₹1,200 = ₹9,600
- Printer: 12 × ₹750 = ₹9,000
Total sales = ₹28,100.
Why is this useful?
You can calculate total sales directly without adding a separate column for each product’s revenue.
Question 2: Calculate Total Employee Payments Using Hours and Rates
A company pays employees according to their working hours and hourly rates. SUMPRODUCT can calculate the total payment for all employees.
Data:
| Employee | Hours Worked | Hourly Rate |
|---|---|---|
| Amit | 160 | ₹250 |
| Priya | 150 | ₹300 |
| Rahul | 170 | ₹200 |
Excel Formula:
=SUMPRODUCT(B2:B4,C2:C4)
Expected Result:
₹1,19,000
Explanation:
- Amit: 160 × ₹250 = ₹40,000
- Priya: 150 × ₹300 = ₹45,000
- Rahul: 170 × ₹200 = ₹34,000
Total employee payments = ₹1,19,000.
Student Task: Add another employee who worked 180 hours at ₹220 per hour. Update the formula to include the new employee.
Expected Result: ₹1,58,600.
Question 3: Calculate Total Inventory Value
An inventory manager wants to know the total value of available stock. Each product has a different stock quantity and purchase cost.
Data:
| Product | Stock Quantity | Cost per Unit |
|---|---|---|
| Notebook | 25 | ₹120 |
| Pen | 40 | ₹85 |
| Calculator | 15 | ₹250 |
Excel Formula:
=SUMPRODUCT(B2:B4,C2:C4)
Expected Result:
₹10,150
Explanation:
- Notebook: 25 × ₹120 = ₹3,000
- Pen: 40 × ₹85 = ₹3,400
- Calculator: 15 × ₹250 = ₹3,750
Total inventory value = ₹10,150.
Student Task: Increase the calculator stock from 15 to 20 units.
Expected Result: ₹11,400.
Question 4: Calculate a Weighted Average Score
A student receives different marks in three subjects. Each subject has a different credit weight, so a simple average would not give the correct weighted score.
Data:
| Subject | Score | Credit Weight |
|---|---|---|
| Excel | 80 | 3 |
| SQL | 75 | 4 |
| Python | 90 | 2 |
Excel Formula:
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)
Expected Result: 80
Explanation:
SUMPRODUCT multiplies each score by its credit weight and adds the results:
- Excel: 80 × 3 = 240
- SQL: 75 × 4 = 300
- Python: 90 × 2 = 180
The weighted total is 720, and the total credit weight is 9.
Weighted average = 720 ÷ 9 = 80.
Student Task: Change the Python score from 90 to 95.
Expected Result: 81.11 when rounded to two decimal places.
Question 5: Calculate Sales Using Multiple Conditions
A company wants to calculate sales for the North region and Product A only. SUMPRODUCT can check both conditions and add only the matching sales amounts.
Data:
| Region | Product | Sales |
|---|---|---|
| North | Product A | ₹1,000 |
| South | Product A | ₹1,500 |
| North | Product B | ₹2,000 |
| North | Product A | ₹2,500 |
| South | Product B | ₹3,000 |
| East | Product A | ₹1,800 |
Excel Formula:
=SUMPRODUCT((A2:A7="North")*(B2:B7="Product A")*C2:C7)
Expected Result: ₹3,500
Explanation:
The formula checks whether each row belongs to the North region and contains Product A. Only rows meeting both conditions are included.
The matching sales amounts are ₹1,000 and ₹2,500.
Total matching sales = ₹3,500.
Student Task: Calculate sales for the South region and Product B.
Expected Result: ₹3,000.
Question 6: Count Employees Who Meet Multiple Conditions
An HR department wants to count employees who work in the Sales department and earn at least ₹50,000.
Data:
| Employee | Department | Salary |
|---|---|---|
| Amit | Sales | ₹55,000 |
| Priya | HR | ₹48,000 |
| Rahul | Sales | ₹62,000 |
| Neha | IT | ₹70,000 |
| Karan | Sales | ₹45,000 |
| Simran | HR | ₹52,000 |
Excel Formula:
=SUMPRODUCT((B2:B7="Sales")*(C2:C7>=50000))
Expected Result: 2 employees
Explanation:
The formula checks two conditions:
- The employee belongs to the Sales department.
- The employee’s salary is at least ₹50,000.
Amit and Rahul meet both conditions, so the result is 2.
Student Task: Count employees in the HR department earning at least ₹50,000.
Expected Result: 1 employee.
Question 7: Calculate Sales After Discounts
A company sells products at different prices and offers different discounts. SUMPRODUCT can calculate total revenue after discounts without creating separate columns for discounted prices.
Data:
| Product | Quantity | Price | Discount |
|---|---|---|---|
| Keyboard | 10 | ₹500 | 10% |
| Monitor | 8 | ₹750 | 5% |
| Mouse | 12 | ₹300 | 0% |
Excel Formula:
=SUMPRODUCT(B2:B4,C2:C4,1-D2:D4)
Expected Result: ₹13,800
Explanation:
The formula calculates each product’s revenue after applying its discount:
- Keyboard: 10 × ₹500 × 90% = ₹4,500
- Monitor: 8 × ₹750 × 95% = ₹5,700
- Mouse: 12 × ₹300 × 100% = ₹3,600
Total revenue after discounts = ₹13,800.
Student Task: Change the keyboard discount from 10% to 15%.
Expected Result: ₹13,550.
Question 8: Calculate Sales for Either of Two Regions
A manager wants to calculate total sales for the North or East region. The formula should include sales from either region.
Data:
| Region | Sales |
|---|---|
| North | ₹1,000 |
| South | ₹1,500 |
| North | ₹2,000 |
| North | ₹2,500 |
| South | ₹3,000 |
| East | ₹1,800 |
Excel Formula:
=SUMPRODUCT(((A2:A7="North")+(A2:A7="East"))*B2:B7)
Expected Result: ₹7,300
Explanation:
In this formula, the plus sign (+) combines the two conditions as an OR test. Sales are included when the region is North or East.
- North sales: ₹1,000 + ₹2,000 + ₹2,500 = ₹5,500
- East sales: ₹1,800
Total sales = ₹7,300.
Student Task: Calculate sales for either the South or East region.
Expected Result: ₹6,300.
Question 9: Calculate the Total Quantity Sold for a Specific Product
A sales manager wants to calculate the total quantity sold for laptops without manually filtering the product list.
Data:
| Product | Quantity Sold |
|---|---|
| Laptop | 3 |
| Mouse | 10 |
| Laptop | 2 |
| Keyboard | 5 |
| Laptop | 4 |
| Mouse | 6 |
Excel Formula:
=SUMPRODUCT((A2:A7="Laptop")*B2:B7)
Expected Result: 9 laptops
Explanation:
The formula checks each product name and adds the quantity only when the product is Laptop.
Total laptops sold = 3 + 2 + 4 = 9.
Student Task: Calculate the total quantity sold for Mouse.
Expected Result: 16 mice.
Question 10: Calculate Average Sales for a Specific Region
A manager wants to know the average sales amount for the North region. The calculation should include only North region records.
Data:
| Region | Sales |
|---|---|
| North | ₹1,000 |
| South | ₹1,500 |
| North | ₹2,000 |
| North | ₹2,500 |
| South | ₹3,000 |
| East | ₹1,800 |
Excel Formula:
=SUMPRODUCT((A2:A7="North")*B2:B7)/SUMPRODUCT(--(A2:A7="North"))
Expected Result: ₹1,833.33
Explanation:
The first SUMPRODUCT calculates total North sales:
₹1,000 + ₹2,000 + ₹2,500 = ₹5,500.
The second SUMPRODUCT counts the North region records: 3.
Average sales = ₹5,500 ÷ 3 = ₹1,833.33.
The double unary operator (--) converts TRUE and FALSE results into 1 and 0 so that the matching records can be counted.
Student Task: Calculate the average sales for the South region.
Expected Result: ₹2,250.
Student Challenge: SUMPRODUCT in a Company Sales Report
Use the following company order data to practice SUMPRODUCT independently.
| Region | Product | Quantity | Price per Unit |
|---|---|---|---|
| North | Laptop | 2 | ₹50,000 |
| West | Laptop | 1 | ₹50,000 |
| West | Mouse | 5 | ₹500 |
| East | Laptop | 3 | ₹50,000 |
| West | Keyboard | 4 | ₹1,500 |
| North | Mouse | 8 | ₹500 |
Tasks
- Calculate the total revenue from all orders.
- Calculate the total revenue for the West region.
- Calculate the total quantity of laptops ordered.
- Calculate the total revenue from laptops in the West region.
Answers
1. Total revenue
=SUMPRODUCT(C2:C7,D2:D7)
Expected Result: ₹3,12,500
2. West region revenue
=SUMPRODUCT((A2:A7="West")*C2:C7*D2:D7)
Expected Result: ₹58,500
3. Total quantity of laptops
=SUMPRODUCT((B2:B7="Laptop")*C2:C7)
Expected Result: 6 laptops
4. West region laptop revenue
=SUMPRODUCT((A2:A7="West")*(B2:B7="Laptop")*C2:C7*D2:D7)
Expected Result: ₹50,000
Key Takeaways
- SUMPRODUCT multiplies corresponding values and adds the results.
- It can calculate total sales, employee payments, and inventory value without helper columns.
- It can calculate weighted averages using
SUMPRODUCTdivided bySUM. - Multiplying logical conditions allows you to calculate totals that meet multiple criteria.
- Adding logical conditions can be used to include records that meet either of two criteria.
- SUMPRODUCT in excel can count records and calculate conditional averages when used with the right formula.
- The ranges used in a calculation should have compatible dimensions.
- For complicated reports, test the formula on a small dataset before applying it to a large worksheet.
Frequently Asked Questions (FAQs)
1. What is the SUMPRODUCT function in Excel?
SUMPRODUCT multiplies corresponding values in arrays and adds the products. It is commonly used for sales calculations, inventory valuation, and weighted averages.
2. What is the syntax of SUMPRODUCT?
The syntax is:
=SUMPRODUCT(array1, [array2], ...)
The arrays contain the values you want to multiply and combine.
3. Can SUMPRODUCT in Excel replace multiplication formulas in separate columns?
Yes. For example, =SUMPRODUCT(B2:B10,C2:C10) calculates the total of each quantity multiplied by its corresponding price without requiring a separate total column.
4. Can SUMPRODUCT handle multiple conditions?
Yes. You can multiply conditions inside the formula to include only records that satisfy all specified conditions.
Example:
=SUMPRODUCT((A2:A10="North")*(B2:B10="Laptop")*C2:C10)
This calculates the total in column C where the region is North and the product is Laptop.
5. Can SUMPRODUCT in Excel calculate a weighted average?
Yes. Divide the weighted total by the sum of the weights.
=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)
Here, column B contains scores and column C contains their weights.
6. What is the difference between SUM and SUMPRODUCT?
SUM adds numeric values directly. SUMPRODUCT can multiply corresponding values before adding them, making it useful for calculations involving quantities, prices, weights, and conditions.
7. Can SUMPRODUCT in Excel count records?
Yes. You can use conditions to count matching records.
Example:
=SUMPRODUCT(--(A2:A10="Sales"))
This counts cells in A2 that contain Sales.
8. Does SUMPRODUCT work with text conditions?
Yes. Text conditions such as "North" or "Laptop" can be used to identify matching rows. However, text values in arrays used for numeric calculations need to be handled carefully.
9. Why does SUMPRODUCT return an unexpected result?
Check that the ranges have compatible dimensions, the numbers are stored as numeric values, and the conditions match the actual data. Also check whether your formula uses AND or OR logic as intended.
10. Is SUMPRODUCT available in modern versions of Excel?
Yes. SUMPRODUCT is supported in many Excel versions, including Excel 2016, Excel 2019, Excel 2021, Excel 2024, and Microsoft 365.

