SUMPRODUCT is one of those Excel functions that looks simple at first—multiply and add—but quickly becomes incredibly useful in real work. Whether you are a data analyst, a small business owner, a teacher grading students, or anyone who works with numbers, SUMPRODUCT can save you time and make your reports cleaner. In everyday terms, it lets you multiply pairs of values and sum the results, and when you combine it with logical tests it can act like many other formulas (SUMIF, COUNTIF, AVERAGEIF) all at once. Let Started!
Example 1: Weighted Average of Exam
Scores
Imagine you want to calculate the weighted average of student scores where each test has a different importance. You list the student names, their scores, and the assigned weight for each score. By multiplying the scores with their weights and dividing by the total weight, you get the exact weighted average.
Formula used:
=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)
Breakdown:
- B2:B4 are
the scores.
- C2:C4 are
the weights.
- SUMPRODUCT(B2:B4,
C2:C4) multiplies each score by its weight and adds them together.
- SUM(C2:C4)
adds up all the weights.
- Division
gives the weighted average.
This formula gives the result 81. It tells us that the weighted average score is slightly higher than a simple average because the higher score had more weight.
Example 2: Conditional Total Sales
with Multiple Criteria
Suppose you have sales data for different products across various regions. If you want to find the sales of laptops in the North region only, SUMPRODUCT makes it easy by checking both conditions together.
Formula used:
=SUMPRODUCT((A2:A6="North")*(B2:B6="Laptop"), C2:C6)
Breakdown:
- (A2:A6="North")
checks which rows belong to the North region.
- (B2:B6="Laptop")
checks which rows are laptops.
- Multiplying
both conditions means only rows where both are TRUE will be included.
- C2:C6
provides the sales numbers.
- SUMPRODUCT
multiplies and adds only matching rows.
The result is 1200, which means only one record matched both conditions.
Example 3: Count of Values Greater
Than Average
If you have a list of sales figures and want to know how many of them are higher than the average, you can use SUMPRODUCT.
Formula used:
=SUMPRODUCT(--(A2:A6 > AVERAGE(A2:A6)))
Breakdown:
- AVERAGE(A2:A6)
finds the average sales.
- (A2:A6
> AVERAGE(A2:A6)) checks which sales are above average.
- --
converts TRUE/FALSE into 1/0.
- SUMPRODUCT
adds the 1s, giving the count.
This returns 2, because only two sales figures are above the calculated average of the dataset.
Example 4: Revenue Only for
High-Rated Products
Sometimes businesses want to sum revenue only for products that meet a quality rating threshold. SUMPRODUCT can filter this easily.
Formula used:
=SUMPRODUCT((C2:C5>=4.5)*B2:B5)
Breakdown:
- (C2:C5>=4.5)
checks which products have a rating of 4.5 or higher.
- This
produces 1 for TRUE, 0 for FALSE.
- Multiplying
by B2:B5 keeps only the revenues of high-rated products.
- SUMPRODUCT
adds those revenues.
The result is 17500, which is the total revenue from products with ratings of 4.5 or higher.
Example 5: Average Sales per Region
To calculate the average sales in the North region, you can use SUMPRODUCT to total the sales for North and divide by the number of records that belong to North.
Formula used:
=SUMPRODUCT((A2:A6="North")*B2:B6) /
SUMPRODUCT(--(A2:A6="North"))
Breakdown:
- (A2:A6="North")*B2:B6
picks up sales only for North.
- SUMPRODUCT
adds them together.
- SUMPRODUCT(--(A2:A6="North"))
counts how many rows belong to North.
- Division
gives the average sales for North.
The result is 1750, which represents the average sales across all North entries.
Example 6: Count Unique Items
If you have a list that repeats items and you want to know how many unique items exist, SUMPRODUCT with COUNTIF can solve this.
Formula used:
=SUMPRODUCT(1/COUNTIF(A2:A6, A2:A6))
Breakdown:
- COUNTIF(A2:A6,
A2:A6) counts how many times each item appears.
- 1/COUNTIF(...)
converts repeats into fractions.
- SUMPRODUCT
adds them, so duplicates add up to 1.
- The result
is the number of unique items.
The result is 3, meaning there are three unique items in the dataset.
Example 7: Sum Sales Above a
Threshold
You may want to add only those sales figures that are greater than a certain value, such as 700.
Formula used:
=SUMPRODUCT((B2:B5>700)*B2:B5)
Breakdown:
- (B2:B5>700)
checks which sales are greater than 700.
- TRUE
becomes 1, FALSE becomes 0.
- Multiplying
by B2:B5 keeps only sales above 700.
- SUMPRODUCT
adds them together.
The result is 2000, because only the sales values 800 and 1200 meet the condition.
Example 8: Compare Two Lists and
Find Matches
Formula used:
=SUMPRODUCT(COUNTIF(B2:B5, A2:A5))
Breakdown:
- COUNTIF(B2:B5,
A2:A5) checks each item in List 1 against List 2.
- It returns
counts of how many times each appears.
- SUMPRODUCT
adds them together, giving total matches.
The result is 4, meaning three items from List 1 are also found in List 2.
Example 9: Weighted Growth Rate
A company may want to calculate growth rates over several years, but some years may carry more importance. SUMPRODUCT helps compute a weighted growth rate.
Formula used:
=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)
Breakdown:
- B2:B4 are
growth percentages.
- C2:C4 are
weights (importance).
- SUMPRODUCT
multiplies growth by weight and adds them.
- Dividing
by total weight gives weighted growth.
The result is 5%, which reflects the weighted effect of each year’s growth.
Example 10: Sum by Month Using Dates
If you want to sum sales for a specific month, such as January, SUMPRODUCT can identify the month from the dates and sum the sales.
Formula used:
=SUMPRODUCT((MONTH(A2:A5)=1)*B2:B5)
Breakdown:
- MONTH(A2:A5)
extracts month numbers from dates.
- (MONTH(...)=1)
checks if the month is January.
- Multiplying
by B2:B5 keeps only January sales.
- SUMPRODUCT
adds them together.
The result is 900, which is the total of all January sales.
Example 11: Average Order Value by
Customer
To calculate the average revenue per order for a particular customer, you can divide their total revenue by the number of their orders using SUMPRODUCT.
Formula used:
=SUMPRODUCT((A2:A4="Sara")*C2:C4) /
SUMPRODUCT((A2:A4="Sara")*B2:B4)
Breakdown:
- (A2:A4="Sara")*C2:C4
picks Sara’s total revenue.
- (A2:A4="Sara")*B2:B4
picks Sara’s total orders.
- Dividing
revenue by orders gives average per order.
The result is 300, showing that Sara’s customers spent an average of 300 per order.
Example 12: Multiple Criteria with
OR Condition
If you want to sum sales for either Pen or Book, SUMPRODUCT allows you to combine conditions with an OR logic.
Formula used:
=SUMPRODUCT(((A2:A5="Pen")+(A2:A5="Book"))*B2:B5)
Breakdown:
- (A2:A5="Pen")+(A2:A5="Book")
checks if the product is Pen OR Book.
- TRUE
values become 1, and either condition can pass.
- Multiplying
by B2:B5 keeps sales for these products.
- SUMPRODUCT
adds them together.
The result is 1000, which includes all Pen and Book sales.
Example 13: Sum of Top Three Sales
To find the total of the top three sales values, you can combine SUMPRODUCT with the LARGE function.
Formula used:
=SUMPRODUCT(LARGE(A2:A6,{1,2,3}))
Breakdown:
- LARGE(A2:A6,{1,2,3})
picks the 1st, 2nd, and 3rd largest values.
- SUMPRODUCT
adds them together.
The result is 2900, which comes from adding the three highest sales values together.
Example 14: Check if All Values Meet
a Condition
If you want to test whether all scores are greater than or equal to seventy, SUMPRODUCT can count and compare them.
Formula used:
=SUMPRODUCT(--(A2:A5>=70))=ROWS(A2:A5)
Breakdown:
- (A2:A5>=70)
checks if each score is at least 70.
- --
converts TRUE/FALSE into 1/0.
- SUMPRODUCT
counts how many meet the condition.
- ROWS(A2:A5)
gives the total number of rows.
- If both
are equal, all scores meet the condition.
The result is TRUE, meaning every score meets the condition.
Example 15: Conditional Variance
To calculate variance only for the North region, you can combine SUMPRODUCT with an IF condition inside AVERAGE.
Formula used:
=SUMPRODUCT((A2:A5="North")*(B2:B5-AVERAGE(IF(A2:A5="North",B2:B5)))^2)
/ SUMPRODUCT(--(A2:A5="North"))
Breakdown:
- (A2:A5="North")
checks which rows belong to North.
- IF(A2:A5="North",B2:B5)
gets sales for North only.
- AVERAGE(...)
finds the mean of North’s sales.
- (B2:B5 -
AVERAGE(...))^2 calculates squared differences.
- SUMPRODUCT
adds squared differences only for North.
- Division
by count of North entries gives variance.
The result is 2500, which represents the spread of sales for the North region.
Conclusion
The SUMPRODUCT function may look advanced, but once you understand
it, it becomes one of the most useful tools in Excel. It can calculate totals,
averages, counts, and even handle multiple conditions—all in one formula.
Instead of writing long and complicated formulas, you can use SUMPRODUCT to get
faster and cleaner results. With practice, you will see how flexible it is and
how it can save you a lot of time in your daily work.
0 Comments