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.

- Below is the syntax of the excel function
**SUMPRODUCT**.SUMPRODUCT(Array1, [Array2], [Array3],…)

- The first array of the
**SUMPRODUCT**function can not be omit. And all the array should has the same size. - The function will multiply all the elements in the multiple array accordingly and then summarize each result and return the number.
- 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. - The above formula will calculate the 2 arrays as
**1*4+2*5+3*6 = 4+10+18=32**. - 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.

- 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 - Column
**A**contains the item name, column**B**contains the item unit price, column**C**contains the item amount. - 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. - 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**. - Select cell
**D2**, and drag the small square on the bottom right corner of the cell to cell**D7**. - 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. - For example, the formula will be changed to
**=B3*C3**in cell**D3**, and be changed to**=B6*C6**in cell**D6**.etc. - Input the formula
**=D2+D3+D4+D5+D6+D7**in cell**F2**and press enter, it will display the number**4651800**in this cell. - If we use the excel
**SUMPRODUCT**function to implement this, we can input the formula**=SUMPRODUCT(B2:B7, C2:C7)**in cell**F1**. - 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.

- 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 - 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. - 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**. - 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}**. - 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}**. - 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}**. - 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.