SUMIFS interview question: summarise sales by region
Turn a raw sales list into a summary by region and product, without a pivot table. The formula interviewers expect, the $ signs that make it copy, and a sheet that grades yours.
SUMPRODUCT function
SUMPRODUCT multiplies the values on each row of two or more ranges and adds up the results. With tests such as (A2:A7="North") inside, it sums or counts only the rows that pass, in any version of Excel.
Available in every version of Excel, Excel for the web and Google SheetsKey takeaways
- SUMPRODUCT multiplies ranges row by row and adds the products: =SUMPRODUCT(B2:B5,C2:C5) is B2*C2 + B3*C3 and so on.
- A test such as (A2:A7="North") gives TRUE or FALSE on each row. Multiply it by a range, or put -- in front, so Excel counts it as one or zero.
- Every range must be the same size, or the result is #VALUE!.
- For plain conditions SUMIFS and COUNTIFS are simpler. Reach for SUMPRODUCT when you need to multiply columns, or to test a calculation on each cell, such as the month of a date.
On this page
SUMPRODUCT syntax
=SUMPRODUCT(array1, [array2], [array3], ...)| Argument | Required | What it means |
|---|---|---|
array1 | Yes | The first range or array. Given only this one, SUMPRODUCT adds it up. |
array2, array3 … | No | More ranges, each the same size as array1. The values on each row are multiplied together, then the products are added. Up to 255 arrays in all. |
Examples
Order total: quantity times price
Each item's quantity times its price, added up, in one formula. Without SUMPRODUCT you'd need a line-total column and a SUM under it.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Order total | |
| 2 | Notebook | 12 | $3.50 | $137.00 | |
| 3 | Pen | 40 | $1.20 | ||
| 4 | Folder | 25 | $0.80 | ||
| 5 | Stapler | 3 | $9.00 |
=SUMPRODUCT(B2:B5,C2:C5) → $137.00Revenue for one region
(A2:A7=E2) is TRUE on the North rows and FALSE on the others. Multiplying turns TRUE into one and FALSE into zero, so only the North rows' units times price are added.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Region | Units | Price | Region | Revenue | |
| 2 | North | 20 | $15 | North | $580 | |
| 3 | South | 35 | $12 | |||
| 4 | North | 10 | $18 | |||
| 5 | East | 25 | $15 | |||
| 6 | North | 5 | $20 | |||
| 7 | South | 15 | $12 |
=SUMPRODUCT((A2:A7=E2)*B2:B7*C2:C7) → $580Count the rows that pass two tests
Two tests multiplied together give one only on a row that passes both, so adding them up counts those rows: East orders above 500. COUNTIFS does the same job here; SUMPRODUCT's way also works when a test needs a calculation.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Order | Region | Amount | East over 500 | |
| 2 | A-1 | East | 750 | 2 | |
| 3 | A-2 | West | 1,200 | ||
| 4 | A-3 | East | 300 | ||
| 5 | A-4 | East | 980 | ||
| 6 | A-5 | West | 450 |
=SUMPRODUCT((B2:B6="East")*(C2:C6>500)) → 2Total one month, which SUMIFS can't do directly
MONTH(A2:A6) works out the month of every date, and the test keeps the ones equal to D2. SUMIFS can only compare the cells as they are, not the month inside them. All the dates here are in 2026; for dates across years, add a YEAR test too.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Date | Amount | Month | Total | |
| 2 | 11 Feb 2026 | 420 | 3 | 775 | |
| 3 | 2 Mar 2026 | 310 | |||
| 4 | 19 Mar 2026 | 275 | |||
| 5 | 7 Apr 2026 | 500 | |||
| 6 | 28 Mar 2026 | 190 |
=SUMPRODUCT((MONTH(A2:A6)=D2)*B2:B6) → 775Practise it
New data, so you can't copy the answer above. We check the number your formula returns, then change the data to make sure it still holds.
Your task: In E2, work out the course grade: each score in B2:B5 counts for the weight beside it in C2:C5.
Graded live| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Part | Score | Weight | Grade | |
| 2 | Quizzes | 82 | 20% | ||
| 3 | Project | 74 | 30% | ||
| 4 | Midterm | 68 | 20% | ||
| 5 | Final exam | 91 | 30% |
Write the formula in the bar above, then press Check or Enter.
Hint
SUMPRODUCT takes the scores and the weights as two ranges, B2:B5 and C2:C5, multiplies them row by row and adds the results.
Solution
=SUMPRODUCT(B2:B5,C2:C5) Any formula that returns the same result passes.
Common mistakes
- Ranges of different sizes. B2:B7 and C2:C6 don't line up, so SUMPRODUCT returns #VALUE!. Make every range start and end on the same rows.
- A test on its own counts nothing. SUMPRODUCT counts text and blanks as zero, and TRUE and FALSE too, so =SUMPRODUCT(B2:B6="East") returns 0 in Excel. Multiply the test by another range or test, or put two minus signs in front of it: =SUMPRODUCT(--(B2:B6="East")) counts the East rows.
- Adding two tests for OR when a row can pass both. Adding tests means "either", but a row that passes both gets counted twice. Test the sum instead: =SUMPRODUCT(((B2:B6="East")+(C2:C6>500)>0)*C2:C6) adds each matching row once.
- Whole columns. =SUMPRODUCT(A:A,B:B) multiplies every one of the 1,048,576 rows in each column, which slows the workbook down. Use ranges that cover the data.
SUMPRODUCT or SUMIFS?
| You need to… | Use |
|---|---|
| Add one column for the rows that match a few conditions | SUMIFS |
| Count the rows that match | COUNTIFS |
| Multiply columns together, then add | SUMPRODUCT |
| Test a calculation on each cell: its month, its length, part of its text | SUMPRODUCT |
| Use “either” logic across different columns | SUMPRODUCT |
SUMIFS and COUNTIFS read more clearly and recalculate faster, so they’re the first choice when they can do the job. SUMPRODUCT covers the rest.
FAQ
What's the difference between SUMPRODUCT and SUMIFS?
SUMIFS adds one range for the rows that meet its conditions, and it is simpler and faster for that. SUMPRODUCT multiplies ranges, and its tests can work on a calculation of each cell, such as the month of a date or the length of a text, which SUMIFS can't.
What does -- do in SUMPRODUCT?
Two minus signs turn TRUE into one and FALSE into zero. SUMPRODUCT counts TRUE and FALSE as zero, so a test on its own needs the -- (or a multiplication) before it can be added up.
Do I need Ctrl+Shift+Enter for SUMPRODUCT?
No. SUMPRODUCT works with arrays in every version of Excel, so you enter it like any other formula. That's why it was the usual way to sum with conditions in older versions.
Can SUMPRODUCT count rows?
Yes. Give it only tests, multiplied together, and it adds up the rows that pass them all: =SUMPRODUCT((B2:B6="East")*(C2:C6>500)).
Guides that use SUMPRODUCT
Worked problems with practice sheets.
#VALUE! error in Excel: what it means and how to fix it
#VALUE! means a formula got the wrong kind of value, most often text where it needs a number. The usual causes and fixes, with a practice sheet.
Know SUMPRODUCT? Prove it on a real sheet.
Practise SUMPRODUCT and the functions around it in a live grid that grades your result and explains your mistakes.
Runs in your browser · Graded on your result · No install