SUMPRODUCT with Multiple Criteria in Excel

The SUMPRODUCT with multiple criteria in Excel helps compare the arrays with numerous criteria.

  • The format for SUMPRODUCTFormat For SUMPRODUCTThe SUMPRODUCT excel function multiplies the numbers of two or more arrays and sums up the resulting products.read more with multiple criteria in Excel will remain the same as the SUMPRODUCT formula. The only difference is that it will have multiple criteria for two or more ranges and then add up those products.In addition, while calculating the SUMPRODUCT with multiple criteria in Excel, we have to use the double negative (–) sign or multiply the formula value with a numeric one (1). The double negative sign is technically called the double unary operator.The double unary operator covers the “TRUE” and “FALSE” into ones and zeroes, respectively.

Format of SUMPRODUCT with single criteria

=sumproduct(–(array1 array2)

OR

=sumproduct((array1 array2)*1)

Format of SUMPRODUCT Multiple Criteria

=sumproduct((array1 array2)*(array3 ))

How to use SUMPRODUCT with Multiple Criteria in Excel?

  • We can use it in place of formulas like SUMIFFormulas Like SUMIFThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12.
  • read more, COUNTIFCOUNTIFThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump”
  • read more, etc.We can use it to create a complex formula that sums up the arrays’ rows and columns.We can use it with logical operatorsLogical OperatorsLogical operators in excel are also known as the comparison operators and they are used to compare two or more values, the return output given by these operators are either true or false, we get true value when the conditions match the criteria and false as a result when the conditions do not match the criteria.read more like AND, OR, & both.

Let us learn this SUMPRODUCT formula with the help of a few examples.

Example #1

Let us assume we have a list of company products in the second column. The third column is for the planned count of products to be sold, and the actual sales happened in column fourth. Now, the company wants to determine how many platinum products sold have made fewer sales than the planned count.

Example #2

Let us assume we have a list of company products in the second column, zone in the third column, and fourth for the planned count of products to be sold. The actual sales that happened are in the fifth column. The company wants to determine how many platinum products sold in the north zone have made less than the planned count.

  • Data for the above case is shown below: In this case, we have two conditions: Firstly, to find the count of the number of products that are less than the planned count. Secondly, that count should be of a platinum product only. Now, we will use the SUMPRODUCT formula to calculate the count with multiple criteria.The final count of sold products that are less than planned, that too product should be platinum is shown below:

  • Data for the above case is shown below:

  • In this case, we have two conditions: Firstly, to find the count of the number of products that are less than the planned count. Secondly, that count should be of a platinum product only. Thirdly, they should sell that product in the “North” zone.

Now, we will use the SUMPRODUCT formula in excelSUMPRODUCT Formula In ExcelThe SUMPRODUCT excel function multiplies the numbers of two or more arrays and sums up the resulting products.read more to calculate the count with multiple criteria.

  • The final count of the number of sold products which are less than planned, that too product should be platinum which lies in “North” zone is shown below:

Things to Remember

  • The wild card characters like an asterisk (*) and question mark (?) are not valid using the SUMPRODUCT formula.There should be the same number of rows and columns in all the SUMPRODUCT formulas. Otherwise, it will return an error.The SUMPRODUCT formula treats all the non-numeric values as zero.Without using a double negative sign or multiple the formula with one, the SUMPRODUCT formula will return an error.

This article is a guide to SUMPRODUCT with Multiple Criteria in Excel. Here, we discuss using the SUMPRODUCT function with multiple criteria in Excel, examples, and a downloadable Excel template. You may also look at these useful functions in Excel: –

  • SUMIF With VLOOKUPSUMIF With VLOOKUPSUMIF is used to sum cells based on some condition, which takes arguments of range, criteria, or condition, and cells to sum. When there is a large amount of data available in multiple columns, we can use VLOOKUP as the criteria.read moreSUMIF Between Two Dates in ExcelSUMIF Between Two Dates In ExcelWhen we wish to work with data that has serial numbers with different dates and the condition to sum the values is based between two dates, we use Sumif between two dates. read moreVLOOKUP with SUMVLOOKUP With SUMVlookup is a very versatile function combined with other functions to get some desired result. One such situation is calculating the sum of the data (in numbers) based on matching values. We can combine the sum function with the Vlookup function as =SUM(Vlookup(reference value, table array, index number, match))read more