SUMPRODUCT Function in Excel – Usage with Examples

The SUMPRODUCT function in Excel is a very versatile function that can replace the SUM Function, COUNTIF Function, and SUMIF Function. The SUMPRODUCT Function can also act as a lookup function.

So let’s start learning.

Version Compatibility – SUMPRODUCT Function

The SUMPRODUCT function was introduced in Excel 2003 and we can use it in the later versions also. The Function needs the CSE method to get correct results. CSE means pressing the Ctrl+Shift+Enter key instead of Enter key when you type the formula as the SUMPRODUCT works on ranges ( group of cells ) or arrays.

We do not need to press the CSE key in the Excel 365 subscriptions and Excel 2022. The SUMPRODUCT works like all other functions of Excel which makes it easier to use.

When to use SUMPRODUCT Function in Excel?

As the name suggests to you, the SUMPRODUCT function multiplies the arrays and then returns the sum of the products. If only one array is supplied to the SUMPRODUCT Function, it works the same as the SUM Function and returns the sum.

The SUMPRODUCT function of Excel might seem complex and pointless after reading its definition but is more worthy to learn than any other excel function.

Syntax and Arguments

=SUMPRODUCT(array1,[array2],[array3],…………….[array254])

Where the function requires the following inputs to be passed to it:-

  • array1 – This is the first range of cells and is a required function argument.
  • [array2] – This is the second range of cells and is an optional function argument.

There can be a maximum of 254 arrays supplied to this function out of which only the first one is mandatory to be passed as a function argument.

The size of all the arrays ( number of cells in one range ) must be the same otherwise the function returns a #VALUE! error.

SUMPRODUCT assumes the blank cells to contain zero.

infographics SUMPRODUCT Function in excel

Example 1 – Basis Usage of SUMPRODUCT Function

Let us say we have the price per product and quantity ordered for that product as follows:-

basic usage of SUMPRODUCT function result

We want to get the total of this bill by multiplying the Price by the Quantity of the product. We can make a separate column to get the price*quantity and then add them all at the end. Or we can simply use the following SUMPRODUCT formula:-

=SUMPRODUCT(B2:B7,C2:C7)
result of using SUMPRODUCT Function in excel

You can see here that in the longer method we calculated the product-wise expense first and then added them by using the sum function.

In the shortest method, we simply used the SUMPRODUCT that returned the results. The function did the product of the ranges (B2:B7 with C2:C7) and then the corresponding products were added.

The SUMPRODUCT simply did this for us:-

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)

Example 2 – Using SUMPRODUCT to replace COUNTIF

Let us suppose that we have the blood group of different students of a class as follows:-

SUMPRODUCT to replace COUNTIF Function in excel

We want to count the total number of students with Blood Group B. Use the following formula:-

=SUMPRODUCT(--(A2:A12="B"))
SUMPRODUCT to replace COUNTIF Function in excel result

As a result, the formula returns 5 as the total number of students having blood group B.

Explanation – We have passed the input to array1 argument of the SUMPRODUCT Function. The condition applies to the range of cells A2:A12 for which the blood group must be equal to B (A2:A12=”B”). This is a condition which is why it will return a TRUE or FALSE for each of the cells in the range A2:A12 as follows:-

SUMPRODUCT to replace COUNTIF Function in excel explanation

And then we applied the double — to this range of boolean values ( TRUE and FALSE) that converted all TRUE’s to 1 and all falses to 0.

SUMPRODUCT to replace COUNTIF Function in excel explanation part 2

Thereafter, the function adds the boolean array of values as:-

=0+1+0+0+1+1+0+0+0+1+1

Therefore the function returns five as the total number of students with blood group B.

The same work can be done with COUNTIF Formula.

Points to Remember about SUMPRODUCT Function

The following points about the SUMPRODUCT formula must be kept in mind for its successful implementation: –

  • After typing the SUMPRODUCT formula in Excel 2019 or earlier versions, we need to press the Ctrl Shift Enter key otherwise the results will be inappropriate.
  • The size of the arrays supplied as a function argument must be the same for all arrays. If it is not equal, then the function returns #VALUE! error
  • The blank cells, text strings, and all numerical values will be treated as 0 by the SUMPRODUCT Function.

Applications of SUMPRODUCT Formula

You can use the SUMPRODUCT Function to learn some of its advanced applications like:-

  • Finding SUM with Condition Applied
  • Searching some value in the range of cells (LOOKUP Function)

This brings us to the end of the blog.

Thank you for reading.

Leave a Comment