< Go Back

Excel SUMPRODUCT Function

In this tutorial, you will learn how to use the Excel SUMPRODUCT function to Multiply, then sum arrays. Furthermore, Excel SUMPRODUCT function is dealing with arrays, multiplying them and returning the sum of the product. Whenever you are dealing with arrays, Excel SUMPRODUCT function is the excellent choice. This function can substitute COUNTIFS and SUMIFS function, summarizing and counting the cells with one or multiple conditions. Formula syntax for Excel SUMPRODUCT function seems like:

=SUMPRODUCT(array1,[array2])

  1.    Excel SUMPRODUCT function to multiple and sum arrays
  2.    Count cells with condition using SUMPRODUCT function
  3.    Sum cells with condition using SUMPRODUCT function

 

  1.     Excel SUMPRODUCT function to multiple and sum arrays

For multiplying and summarizing arrays you can use SUMPRODUCT function. Formula syntax is very simple, array1 is required part of the function while the other arrays are the optional part of the formula syntax:

=SUMPRODUCT(array1,[array2])

=SUMPRODUCT($B$3:$B$9,$C$3:$C$9)

 

Excel SUMPRODUCT function to multiple and sum arrays

In the example, we want to multiply the numbers from Array 1 with the numbers from Array 2 in each row and to summarize the product. If we had just put one array in the formula syntax, Excel SUMPRODUCT function would have behaved as SUM function.

Some important things to remember regarding Excel SUMPRODUCT function:

  1. Arrays must have the same size, otherwise, the formula result will be an error
  2. Non-numeric values in arrays will always have the value of zero
  3. Booleans {TRUE, FALSE} will convert into numbers {1,0} using double negation “–” in front of the logical test inside the array

 

  1.     Count cells with condition using SUMPRODUCT function

Excel SUMPRODUCT function can replace COUNTIFS function, for counting the number of cells in the defined arrays that meet a certain condition. Formula syntax for conditionally counting will look like:

=SUMPRODUCT(--(array1=condition))

=SUMPRODUCT(--($C$3:$C$9=$F3))

 

Count cells with condition using SUMPRODUCT function

 

In the example above, we want to calculate the number of products per product type. Array1 will be the column Product, where we want to count the cells. The condition is the cell that we want to count, “Product A” in cell F3.

In each row in column “Product”, SUMPRODUCT function is checking if the condition is met. A formula will return Boolean TRUE if the condition is met, and in contrast, formula will return Boolean FALSE:

Count cells with condition using SUMPRODUCT function- Booleans TRUE and FALSE

We have to translate Booleans into numeric values {1,0} using double negation in front of the formula part with the logical test. After this formula part, TRUE values will become number 1 (condition is met) while FALSE values will become 0 (condition is not met):

Translating Booleans into numeric values {1,0} in SUMPRODUCT function

Now Excel SUMPRODUCT function summarize only values with number 1, while the initial condition is true. Finally, the result is the same as the COUNTIF function.

  1.     Sum cells with condition using SUMPRODUCT function

Rather than using a SUMIFS function, we can use SUMPRODUCT function for summarizing cells under certain condition. A formula will look like:

=SUMPRODUCT(sum range,--(criteria range=criteria))

=SUMPRODUCT($D$3:$D$9,--($C$3:$C$9=$F3))

 

Sum cells with condition using SUMPRODUCT function

In the example above we want to calculate the profit per project. As we have shown in counting SUMPRODUCT function example, first of all, we have to find the cells in the criteria range, column “Product”, that meet the condition of the certain product type, “Product A”. As a result, this formula part array will look like:

Translating Booleans into numeric values {1,0} in SUMPRODUCT function

Array2 in SUMPRODUCT function will be: {1,0,1,0,0,1,1}, allocating number 1 to the cell in the array that meets the condition, and zero to all cells that do not meet the condition. Finally, you probably understand the logic behind the formula syntax.

The SUMPRODUCT function will multiple the Array 2 with Array1 with profit data. Whenever the condition is not met, we will multiply profit value with zero, and the result will be zero indicating that profit is not taken into account into the calculation. If the condition is true, we will multiply number 1 with profit data. As a result, we will get the summarized profit per each product type.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar