SUMPRODUCT Function with Multiple Criteria (Table of Contents)
SUMPRODUCT Function with Multiple Criteria
In Microsoft, excel SUMPRODUCT is a built-in function which is used to multiply the range of cells or arrays and return the sum of products. This sunproduct function first multiplies the value and then add the input value array, from the name (SUM PRODUCT) itself we can assume that it will multiply and sum the value.
Syntax for SUMPRODUCT:
Parameters:
array1:-Which denotes the first array or range that we need to multiply and then it will add the value subsequently.
array2:- Which denotes the second array or range that we need to multiply and then it will add the value subsequently.
SUMPRODUCT function normally multiplies the ranges or arrays together and then returns the sum of products. This “SUMPRODUCT” is a versatile function where it can be used to count and sum like COUNT IFS and SUMIFS function. We can find the SUMPRODUCT built-in function in excel which is categorized under the MATH/TRIG function where we can find it in the formula menu which is shown in the below screenshot.
How to use SUMPRODUCT Function with Multiple Criteria?
Let’s understand how to use SUMPRODUCT Function with Multiple Criteria by using some examples.
SUMPRODUCT Function with Multiple Criteria – Example #1
Using SUMPRODUCT in excel:
In this example, we will learn how to use the SUMPRODUCT function using the simple arithmetic values.
Consider the below example which has some random numbers in two columns which are shown below.
Assume that we need to multiply both the A and B column values and sum the result. We can do it by applying the SUMPRODUCT function by following the below steps.
Step 1 – Click on the cell where you want the result.
Step 2 – Apply the SUMPROODUCT formula as shown below.
In the above screenshot, we have applied the SUMPRODUT function with A2:A4 as array1 and B2:B4 as array two which will multiple both the values and sum the result.
Step 3 – Click the Enter key and we will get the final output as shown below.
Result:
In the below screenshot we can see that SUMPRODUCT has returned the output of 4470. Where this SUMPRODUCT function calculates the array as =(450*2)+(450*2)+(890*3)=4470
SUMPRODUCT Function with Multiple Criteria – Example #2
Using Multiple SUMPRODUCT:
In this example, we will see how to use multiple SUMPRODUCT function by using student’s marks scored in the semester as shown below.
Consider the above example where we need to multiply both A and B column values and calculate the average by following the below steps.
Step 1 – Create a new column for output.
Step 2 – Apply the SUMPRODUCT function =SUMPRODUCT(C2:C7,D2:D7)/SUM(D2:D7) which is shown below.
Step 3 – Click the Enter key so that we will get the average result percentage as 55 % which is shown in the below screenshot.
In the above screenshot we applied the SUMPRODUCT function with first array as score values C2 to C7 and second array as Grade Value D2 to D7 so that we sum product will multiple the values first where if we do manual calculation we will get the product value as 498 and the sum value as 9 and divide the Product value by sum value which will give you the same result as 55 percent which is shown as the manual calculation in the below screenshot.
Manual Calculation:
SUMPRODUCT Function with Multiple Criteria – Example #3
Using TRUE & FALSE in SUMPRODUCT with Criteria:
In this example, we will learn how to apply SUMPRODUCT to get the specific criteria based data.
Consider the below example which shows employee database with their EmpID, Name, Designation, KRA and Monthly Salary.
Assume that we need to check how many employees are working in specific “KRA” and what their total grand salary is?. In such cases, we can easily calculate the output by applying the SUMPRODUCT function by following the below steps.
- Create a new row for output column.
- Apply the below SUMPRODUCT function as follows.
=SUMPRODUCT(–(E3:F20=”MYNTRA”),F3:F20)
- In the above screenshot, we have used the SUMPROODUCT function with first part of array as (–) double negative because excel will usually convert this as TRUE and FALSE values where TRUE=1 and False =0
- The second part of an array will look for G4:G21=”MYNTRA”
- The third part of an array will look for the contents values from H4:H21
- Press the Enter key we will get the output as 24000 which is shown as the result in the below screenshot.
Let’s see how the SUMPRODUCT function works with the (–) double negative values.
This SUM PRODUCT function will check the two arrays if the given value is TRUE it will multiple with 1 and if the given value is false it will multiply with 0 with the below calculation as follows.
SUMPRODUCT function check for the first array in G column if the KRA is “MYNTRA” excel will consider this as TRUE with the value 1 i.e. =1*6000=6000. If the array in G column is not “MYNTRA” excel will consider this as FALSE with the value 0 i.e. 0*6000=0.
SUMPRODUCT Function with Multiple Criteria – Example #4
Using SUMPRODUCT as COUNT function:
In this example, we will see how SUMPRODUCT function works as a COUNT function.
Consider the same employee database where we need to count the number of AMAZON employees from the database given in example 3 by following the below steps as follows.
- Create a new row for count.
- Apply the SUMPRODUCT function =SUMPRODUCT (–(G4:G21=”AMAZON”)) as shown in the below screenshot.
- In SUMPRODUCT function we can see that we have applied (–) double negative to represent the value as TRUE or FALSE.
- The first part of an array will check in the G column if the KRA is AMAZON excel will treat it as 1 (TRUE)or else excel will treat as 0 (FALSE)
- Click the Enter key so that we get the number of count for Amazon employee as four which is shown as the result in the below screenshot.
Things to Remember
- SUM product will normally accept 255 arguments.
- SUMPRODUCT can be used in many functions like VLOOKUP, LEN, and COUNT.
- SUMPRODUCT function will throw and #VALUE! Error if the array dimension values are not in the same range
- SUMPRODUCT function can also be used as a COUNT function.
- In SUMPRODUCT function if we simply provide only one array value, SUMPRODUCT function will just sum the values as output.
Recommended Articles
This has been a guide to SUMPRODUCT Function with Multiple Criteria in Excel. Here we discussed SUMPRODUCT Function with Multiple Criteria in Excel and how to use SUMPRODUCT Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –
