**What Is SUMPRODUCT With Multiple Criteria In Excel?**

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.

For example, consider the below table showing sample with expected and actual sales. Now, assume that we have to find the sample with less sales than the expected sales.

The steps are:

**Step 1:** Firstly, we should insert the SUMPRODUCT formula in the desired cell.

**Step 2:** The formula is =SUMPRODUCT((C2:C6<B2:B6)*(A2:A6=”B”))

Likewise, we can use the SUMPRODUCT formula with multiple criteria in Excel.

##### Table of contents

### Key Takeaways

- SUMPRODUCT with multiple criteria is a method used to obtain results using SUMPRODUCT formula with criterias.
- We can use this function in place of
**formulas like SUMIF**,**COUNTIF**, etc. - Also, we can use it to create a complex formula that sums up the arrays’ rows and columns.
- Similarly, users can use SUMPRODUCT function in Excel with
**logical operators**like AND, OR, & both. - The wild card characters like an asterisk (*) and question mark (?) are not valid using the SUMPRODUCT formula.

### How To Use SUMPRODUCT With Multiple Criteria Excel Function?

- 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 following examples.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tuto**rials)

**–>>** **If you want to learn Excel and VBA professionally**, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from **Basic Excel to Advanced Excel, Macros, Power Query, and VBA.**

### 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.

Below are the steps of using SUMPRODUCT with multiple criteria in Excel:

**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:

#### 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. 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:

#### Example #3

Let us assume that student Andy has completed his exams and has a list of expected and obtained marks in various subjects. Column A shows the subject and columns B and C shows the expected and obtained marks.

- In this case, we have two conditions: Firstly, to find the count of subjects where he has obtained less than the expected marks.

Now, we will use the **SUMPRODUCT formula in excel** to calculate the count with multiple criteria.

We can see the result as shown in the below image.

**Important Things To Note**

**Important Things To Note**

- 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.

### Frequently Asked Questions

**1. What are some important points to remember while using SUMPRODUCT with multiple criteria?**

• 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.

**2. Explain SUMPRODUCT with multiple criteria with an example.**

For example, consider the below table showing sample with expected and actual sales. Now, assume that we have to find the sample with less sales than the expected sales.

The steps are:**Step 1:** Firstly, we should insert the SUMPRODUCT formula in the desired cell.**Step 2:** The formula is =SUMPRODUCT((C2:C6<B2:B6)*(A2:A6=”Orange”))

Likewise, we can use the SUMPRODUCT formula with multiple criteria in Excel.

**3. What is the formula for SUMPRODUCT with multiple criteria?**

The formula for format of SUMPRODUCT with single criteria

• **=sumproduct(–(array1 <Condition> array2)• =sumproduct((array1 <Condition> array2)*1)**

The formula for format of SUMPRODUCT Multiple Criteria

**• =sumproduct((array1 <Condition1> array2)*(array3 <condition2>))**

### Recommended Articles

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 more
- SUMIF 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 more
- VLOOKUP 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

## Leave a Reply