How To Use Excel SUMPRODUCT Formula Example

The excel function SUMPRODUCT is very useful when you want to use a formula to calculate the summary of multiple number arrays. This article will tell you how to use the excel SUMPRODUCT formula with examples.

1. The Excel SUMPRODUCT Function Syntax.

1. Below is the syntax of the excel function SUMPRODUCT.

   SUMPRODUCT(Array1, [Array2], [Array3],…)

2. The first array of the SUMPRODUCT function can not be omit. And all the array should has the same size.
3. The function will multiply all the elements in the multiple array accordingly and then summarize each result and return the number.
4. For example, input the formula =SUMPRODUCT({1,2,3},{4,5,6}) in any excel worksheet cell and press enter key, then it will display the number 32 in the cell.
5. The above formula will calculate the 2 arrays as 1*4+2*5+3*6 = 4+10+18=32.
6. If you just pass one array to the excel function SUMPRODUCT in a formula such as =SUMPRODUCT({1,2,3}), then it will add all the numbers in the array and return the result number 6 ( 1 +2 +3 ).

2. How To Use Excel SUMPRODUCT Formula To Calculate Total Sales Revenue.

1. Below is the example data cells.
   

   	A	B	C	D	E	F
   1	Sales Item	Unit Price($)	Item  Amount	Item Revenue	Total Revenue	4651800
   2	iPhone	1000	1299	1299000		4651800
   3	iPad	900	1000	900000
   4	MacBook Pro	1500	600	900000
   5	MacBook Air	1800	500	900000
   6	Mac Mini	1000	360	360000
   7	Apple Watch	800	366	292800

2. Column A contains the item name, column B contains the item unit price, column C contains the item amount.
3. If we want to calculate the total revenue of all the sales items, we need to calculate each item revenue by Unit Price * Item  Amount, then add up all the item’s sales revenue together.
4. We can input the formula =B2*C2 in cell D2 and press the enter key, then it will display the result number in cell D2.
5. Select cell D2, and drag the small square on the bottom right corner of the cell to cell D7.
6. Then it will auto fill the same formula to each of the cells in cell range D3:D7, but the formula will change the cell number in the formula to the current row number.
7. For example, the formula will be changed to =B3*C3 in cell D3, and be changed to =B6*C6 in cell D6 .etc.
8. Input the formula =D2+D3+D4+D5+D6+D7 in cell F2 and press enter, it will display the number 4651800 in this cell.
9. If we use the excel SUMPRODUCT function to implement this, we can input the formula =SUMPRODUCT(B2:B7, C2:C7) in cell F1.
10. When we press enter key, it will show the same number as in cell F2, so this can confirm the calculate result number is same and correct.

3. How To Use Excel SUMPRODUCT Formula To Calculate For Sales Item Revenue.

1. Now we add a column For Sale in the above sales table like below.
   

   	A	B	C	D	E	F	G
   1	Sales Item	Unit Price($)	Item  Amount	For Sale	Item Revenue	Total Revenue	4651800
   2	iPhone	1000	1299	YES	1299000
   3	iPad	900	1000	NO	900000	For Sale Item Revenue	2559000
   4	MacBook Pro	1500	600	NO	900000
   5	MacBook Air	1800	500	YES	900000
   6	Mac Mini	1000	360	YES	360000
   7	Apple Watch	800	366	NO	292800

2. If the for sale column’s cell value is YES then it means the item is ready for sale, if the value is NO then it means the item is not ready for sale.
3. We want to calculate the For Sale Item Revenue in cell G3, so we can input the formula =SUMPRODUCT(–(D2:D7=”YES”),B2:B7,C2:C7) in cell G3 and press the enter key, then it will summarize the item revenue which are ready for sale in cell G3, you can see the number 2559000 in cell G3.
4. We will explain the formula step by step. The expression (D2:D7=”YES”) will return an array of Boolean values, you can input the formula =D91:D96=”YES” in any cell to get the Boolean array {TRUE, FALSE, FALSE,TRUE, TRUE, FALSE}.
5. When you input the formula =-(D2:D7=”YES”) in any cell, it will convert the Boolean array to a number array {-1,0,0-1,-1,0}.
6. We need positive numbers in the array, so we add a minus character before the above expression, you can change the above formula to =–(D2:D7=”YES”), it will return a positive number array {1,0,0,1,1,0}.
7. Now the formula =SUMPRODUCT(–(D2:D7=”YES”),B2:B7,C2:C7) will be changed to=SUMPRODUCT({1,0,0,1,1,0},B2:B7,C2:C7), it will multiply the 3 arrays elements accordingly and return the summarized result number.