Excel Functions Tutorials
- Maths Functions in Excel
- ABS Function in Excel
- AGGREGATE Excel Function
- CEILING Excel Function
- COMBIN Excel Function
- COS Excel Function
- EVEN Function in Excel
- EXPONENTIAL Excel Function
- Excel Matrix Multiplication
- Excel Minus Formula
- FLOOR Function in Excel
- Histogram Formula
- INT Excel Function (Integer)
- LN Excel Function
- LOG Excel Function
- Mode in Excel
- ODD Function in Excel
- POWER Function in Excel
- PRODUCT Excel Function
- PRODUCT Formula in Excel
- PI in Excel
- Quotient in Excel
- Running Total in Excel
- RAND Excel Function
- Rand Excel Formula
- RANDBETWEEN in Excel
- MROUND in Excel
- ROUND in Excel
- ROUND Formula in Excel
- ROUNDDOWN Excel Function
- ROUNDUP Function in Excel
- SIGN Excel Function
- SIN Excel Function
- Square Root in Excel (SQRT)
- SUBTOTAL Excel Function
- SUM Function in Excel
- SUM Formula in Excel
- Excel SUM Shortcut
- SUMIF in Excel
- SUMIFS in Excel
- SUMIF Not Blank
- SUMIFS with Dates
- SUMIF Between Two Dates
- Sumif Text in Excel
- SumIf with Multiple Criteria
- SUMPRODUCT Function in Excel
- SUMPRODUCT Formula in Excel
- SUMPRODUCT with Multiple Criteria
- How to Sum Multiple Rows in Excel?
- TAN Excel Function
- TANH in Excel
- Average vs Weighted Average
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
- Power Bi (35+)
SUMPRODUCT is in itself a very useful function and we know what it does, but it is very little known that this is one of the most sought functions to calculate sumproduct on based of many criteria’s, we can use the arithmetic operators in this function to make a criteria and we can have multiple criteria in a similar fashion.
Sumproduct with Multiple Criteria (Table of Contents)
SUMPRODUCT with Multiple Criteria in Excel
SUMPRODUCT is not just multiplying the values of two or more arrays & then adding up the product of those, in fact, it is far more useful. It is a unique & effective way to deal with more challenging problems.
SUMPRODUCT with Multiple Criteria in excel helps in comparing the different arrays with multiple criteria’s.
- The format for SUMPRODUCT with Multiple Criteria in excel will remain the same as of Sum product formula, the only difference is that it will have multiple criteria’s for to multiple two or more ranges & then adding 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 numeric one (1). The double negative sign is technically called the double unary operator.
- Double Unary operator coverts the ‘TRUE’ and ‘FALSE’ into ones and zero respectively.
Format of SUMPRODUCT with single criteria
=sumproduct(–(array1 <Condition> array2)
=sumproduct((array1 <Condition> array2)*1)
Format of SUMPRODUCT Multiple Criteria
=sumproduct((array1 <Condition1> array2)*(array3 <condition2>))
How to use SUMPRODUCT with Multiple Criteria in Excel?
- It can be used in place of formulas like SUMIF, COUNTIF, etc.
- It can be used to create a complex formula that sums up both the rows & columns of all the arrays.
- It can be used with logical operators like AND, OR & with both as well.
Let us learn this SUMPRODUCT formula with the help of a few examples.
SUMPRODUCT with Multiple Criteria Example #1
Let us assume, we have a list of products of a company in the 2nd column, 3rd column is for the planned count of products to be sold, and the actual sales happened in column 4th. Now, the company wants to find out how many Platinum products sold have made fewer sales 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, which are less than the planned count & secondly, that count should be of a Platinum product only.
Now, we will use the sum-product formula, to calculate the count with multiple criteria.
- The final count of a number of sold products which are less than planned, that too product should be platinum is shown below,
SUMPRODUCT with Multiple Criteria Example #2
Let us assume, we have a list of products of a company in the 2nd column, Zone is in the 3rd column, 4th is for the planned count of products to be sold, and the actual sales happened in column 5th. Now, the company wants to find out how many Platinum products sold in North Zone have made less count 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, which are less than the planned count, secondly, that count should be of a Platinum product only & thirdly that product should be sold in North Zone.
Now, we will use the SUMPRODUCT formula, 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 about SUMPRODUCT with Multiple Criteria in Excel
- Wild card characters like an asterisk (*), question mark (?) are not valid while using SUMPRODUCT formula.
- There should be the same number of rows & columns in all the array of SUMPRODUCT formula; otherwise, it will return an error.
- SUMPRODUCT formula treats all the non-numeric values as zero.
- Without using a double negative sign or multiple the formula with one, SUMPRODUCT formula will return an error.
You can download this SUMPRODUCT with Multiple Criteria Excel template here – SUMPRODUCT with Multiple Criteria Excel Template
This has been a guide to SUMPRODUCT with Multiple Criteria in Excel. Here we discuss how to use SUMPRODUCT Function with Multiple Criteria in Excel along with examples and downloadable excel template. You may also look at these useful functions in excel –
- SUMIF With VLOOKUP
- SUMIF Between Two Dates in Excel
- Excel Count Rows which has only the Numbers
- Examples of using COUNTIF Function
- Examples of VLOOKUP with SUM Function
- Apply VLOOKUP with Multiple Criteria
- Use COUNTIF with Multiple Criteria
- Use Product Excel Function
- How to Insert Multiple Rows in Excel?