Your orders table holds quantity, price and region, and you need the total amount for a single region without creating a subtotal column that nobody remembers to delete later.
How to use SUMPRODUCT Formula step by step
1. Choose the calculation
Sum of products of two or three ranges, a sum under one or two conditions, a count or a weighted average.
2. Give the ranges
They must all be the same size. Conditions ask for the range being tested, the operator and the value.
3. Copy and check
Paste the formula in the total cell. SUMPRODUCT never needs to be confirmed as an array formula in any version.
The Step Count: Traditional Friction vs. KorexTools
Building the total with helper columns and filters (4 steps)
- Add a quantity-times-price column.
- Filter the region you care about.
- Add up only the visible cells.
- Delete the helper column and redo it when the region changes.
With the KorexTools “SUMPRODUCT Formula” builder (3 steps)
- Choose the calculation.
- Enter ranges and conditions.
- Copy one formula that works for any region.
One formula that replaces the helper column and the filter
SUMPRODUCT multiplies the items of the ranges row by row and adds the outcome, so quantity times price comes out without a subtotal column. Its real value shows with conditions: a comparison in brackets yields TRUE or FALSE, and multiplied by a range it lets through only the rows that qualify.
To count rows you prefix a double minus, which turns the TRUEs into ones. With two conditions both brackets are multiplied, something SUMIF cannot do without moving to SUMIFS, and the weighted average comes from dividing by the sum of the weights.
When SUMPRODUCT is the right pick
- •A quantity-times-price total with no extra column.
- •Sums under combined conditions that do not fit SUMIF.
- •A weighted average of grades, prices or lead times.
Regions North, South and North with quantities 2, 3 and 5 and prices 10, 20 and 30
| Calculation | Formula | Result |
|---|---|---|
| Quantity times price total | =SUMPRODUCT(B2:B4,C2:C4) | 230 |
| Quantity for North only | =SUMPRODUCT((A2:A4="North")*B2:B4) | 7 |
| Weighted mean price | =SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4) | 23 |
Common Mistakes and How to Avoid Them
- ⚠️Comparing ranges of different sizes.
- ⚠️Forgetting the brackets around each condition.
Frequently Asked Questions
Why does SUMPRODUCT error with ranges of different sizes?
Because it pairs items one to one. If one range has ten rows and another nine, it returns #VALUE!. Check that the ranges start and end on the same rows.
What is the double minus for?
It turns TRUE and FALSE into 1 and 0. Without it the comparison alone cannot be added and the count comes out as zero.
When should I use SUMIFS instead of SUMPRODUCT?
When the conditions are simple, since it is faster on thousands of rows. SUMPRODUCT wins when you need operations between ranges inside the condition.
What I would do if I were in your shoes
«A long formula stays maintainable when every condition sits in brackets and every range has a name.» — DataKorex Engineering Team.