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 above scenario to get the grand total can be:
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:
SUMPRODUCT(array1, [array2], [array3], …)
- The first argument is the array1 or range1 that you want to multiply and get the sum. This is required.
- The second, third and so on arrays are optional.
- The SUMPRODUCT function treats any non-numeric array item as zero.
- The dimensions of arrays must be same, otherwise, SUMPRODUCT results in a #VALUE error.
An example of 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:
A2 * B2 = 30
A3 * B3 = 10
A4 * B4 = 29
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 SUMPRODUCT function got the calculation:
This is how SUMPRODUCT calculated the sum after multiplications:
A2 * C2 = 10 & B2 * D2 = 15
A3* C3 =8 & B3 * D3 = 6
A4 * C4 = 15 & B4 * D4 = 8
A5 * C5 = 10 & B5 * D5 = 20
Total after multiplications = 92
The example of using 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:
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:
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 F9 key after selecting the A2:A7=”Rice” in the formula bar. This is how it displays for 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 non-numeric item
As mentioned earlier, if an array item is a non-numeric then SUMPRODUCT takes it as zero. The following example shows how:
You can see, B5 and B7 are given non-numeric values and no error resulted. Instead, SUMPRODUCT taken those as zeros and the result for both is zero. For the rest of items, you may calculate the sum which is 91.75.
The VALUE error if dimensions of arrays are not 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 work. This formula will also work:
As such, the dimensions are the same. In this case, this is how SUMPRODUCT will multiply and sum:
A4 * B2
A6 * B4
A7 * B5
Getting the SUM of only specific product example
As shown in the count example by using SUMPRODUCT function that 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.
For demonstrating 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:
For understanding 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 it to what SUMPRODUCT returned.
Getting the character count by using SUMPRODUCT with LEN function
In the LEN function tutorial, we learned how to get the character count of a cell by using LEN function. For example:
You can’t provide a range in 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, the B5 cell displayed the total count of characters in A2 to B3 cells which is 17. Is not that easy and cool way for getting 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.
Just replace the character that you want to replace by ‘h’ in the formula.