SUMPRODUCT Function in Excel

Consider, you have Items, Quantity, and Price columns in an Excel sheet.You require the total after (Quantity * price) columns for each row along with grand total:What could be the easy way?

The SUMPRODUCT function is used to multiply corresponding arrays or ranges and returns the sum.

So, the formula for the above scenario to get the grand total can be:

=SUMPRODUCT(A2:A6,B2:B6)

I will explain how it worked in the later section; first, let us look at the generic syntax of SUMPRODUCT.

Syntax of SUMPRODUCT function

The general syntax of using the SUMPRODUCT function is:

An example of the SUMPRODUCT function

For the first example of explaining how SUMPRODUCT function works, let us look at the same example as used in the first section.

That is, getting the Grand total of Quantity * price columns. The resultant sheet of the formula is:

I provided two arrays in the SUMPRODUCT function. The first is A2:A6 and the second is B2:B6. The matrix is:

And so on. If you calculate the total, it should be 570.

The example with four columns

For this example, four columns are used as ranges in the SUMPRODUCT function. See how the SUMPRODUCT function got the calculation:

This is how SUMPRODUCT calculated the sum after multiplications:

Total after multiplications = 92

The example of using a single array

If you use a single array in the SUMPRODUCT function then it will simply sum the array items. In the following example, only the price column range is given in the SUMPRODUCT function:

SUMPRODUCT formula:

The example of getting the count by SUMPRODUCT function

The SUMPRODUCT function is versatile. You may also use it to get the count of an item in the given array/range.

To demonstrate that, I added another column to the example Excel sheet i.e. Product Name. In the example, we will get the count of Rice by using the SUMPRODUCT function:

The formula:

=SUMPRODUCT(--(A2:A7="Rice"))

You can see the “Rice” occurred three times in the Excel sheet.

The evidence of SUMPRODUCT returns TRUE/FALSE can be seen if you press the F9 key after selecting the A2:A7=”Rice” in the formula bar. This is how it displays for the above example:

You see, it displayed TRUE three times that are converted into 1. If you compare the order of TRUE with the above Excel sheet, these are the occurrence of “Rice”.

The SUMPORUDUCT example for a non-numeric item

As mentioned earlier, if an array item is non-numeric then SUMPRODUCT takes it as zero. The following example shows how:

You can see that B5 and B7 are given non-numeric values and no error resulted. Instead, SUMPRODUCT takes those as zeros and the result for both is zero. For the rest of the items, you may calculate the sum which is 91.75.

The VALUE error if the dimensions of arrays are not the same

The SUMPRODUCT function requirement is the dimensions of the arrays must be the same; otherwise, a #VALUE error is raised by Excel. See the example below where different dimensions are given for arrays:

Not necessarily, you need to use the same number for cells e.g. B2:B7 in order to SUMPRODUCT function to work. This formula will also work:

As such, the dimensions are the same. In this case, this is how SUMPRODUCT will multiply and sum:

Getting the SUM of only specific product example

As shown in the count example by using SUMPRODUCT function how we used a logical test for getting the count of product “Rice”. You may also extend this and get the sum of the specific product only.

To demonstrate that, we will use three-column table again that contains Product Name, Price, and Quantity.

In the SUMPRODUCT function, we will filter the “Rice” product only and get the sum after (price * quantity) of all occurrences.

Have a look at the formula and output:

=SUMPRODUCT(--(A2:A7="Rice"),B2:B7,C2:C7)

To understand this, I have also added the fourth column that contains the total of (price * quantity) for each product. You may calculate the highlighted Rice totals and compare them to what SUMPRODUCT returned.

Getting the character count by using SUMPRODUCT with LEN function

By using the Excel LEN function, we can get the character count of a cell. For example:

You can’t provide a range in the LEN function, so either use the SUM function technique (demo given in above linked tutorial) or add cells one by one in the formula like this:

Another easier way to get the character count for the range or array of items is by using the SUMPRODUCT with LEN function. You may provide a range like this:

The Excel sheet result:

You can see that the B5 cell displayed the total count of characters in A2 to B3 cells which is 17. Is not that easy and cool way to get the count of characters?

Getting the count of specific character example

By using SUMPRODUCT, LEN, and SUBSTITUTE functions together, you may get the count of a specific character from the given array or range of cells.

See the following example and formula where we will get the total count of character ‘h’ in A2:B6 range.

=SUMPRODUCT(LEN(A2:B6)-LEN(SUBSTITUTE(A2:B6,"h","")))

The result:

Just replace the character that you want to replace by ‘h’ in the formula.