How to Use SUMPRODUCT with Multiple Criteria in Excel

Do you have data in multiple columns you need to add up, but only when specific criteria are met? Do you have a tough time making Excel formulas do the work for you?

If so, a SUMPRODUCT with multiple criteria is a great solution to simplify your data crunching.

In this article, we will walk through the steps in the following:

• What is Sumproduct with multiple criteria?

• The Formula for Sumproduct with Multiple Criteria

• Example of How to Use Sumproduct with Multiple Criteria

• Here are a few guides on troubleshooting errors with Sumproduct

• Frequently asked questions on Beginners Guide on Sumproduct with Multiple Criteria in Excel.

Read Also: Excel Repeat Last Action: What is it and How Does it Work?

 What is the SUMPRODUCT function?

The Sumproduct function in Excel allows you to multiply arrays and add the total of those products.

The Sumproduct with multiple criteria function lets you analyze arrays or ranges of data by multiplying true and false values. It's a great way to get information from multiple criteria simultaneously quickly!

Sumproduct can be used to find percentages or discounts, add products, and quickly analyze large datasets.

The Formula for Sumproduct with Multiple Criteria

The Sumproduct with multiple criteria is a formula that allows users to get information based on specific conditions.

This makes it easier and faster than looking through a long list. It multiplies values that meet certain conditions and can support up to 254 ranges. This means the formulas are more clever and save time when analyzing large amounts of data or trying to figure out something specific.

The following formula for Sumproduct with multiple criteria. 

=SUMPRODUCT((array 1 criteria)*(array 2 criteria)*array values)

The formula for Sumproduct with single criteria.

=Sumproduct((array1 <Condition> array2)*1)

Read Also: Excel Macro Button: What is it and How to Create One

How to Create a SUMPRODUCT With Multiple Criteria

You can follow these step to create a SUMPRODUCT formula with multiple criteria in Excel:

1.  Prepare your data.

• The set of numbers represents the data range of values.

• The first column represents the list of Days.

• The first row represents the list of Names.

2. Choose the Name and the Day of you want to extract the criteria matching Name across rows and matching Day across columns.

3. Enter the Sumproduct with multiple criteria formula at the Sum Value cell or the cell I:4

• The formula for the Sumproduct example:

• =SUMPRODUCT((C2:C10<B2:B10)*1)

4. Select the set of numbers from cell B2:F8, or you can type the cell reference to the open bracket and then close the bracket after typing and completing Array 1.

5. Click row range B1:F1 and Click "Vince" at the I2 cell; this will be your first condition, then close the bracket.

6. Multiply Array 2 by Array 3, Click the column range A2:A8, then Click Monday at the I3 cell as your condition and close your bracket.

7. Press enter key to see the result as 42.

How to Use a Conditional Format to Highlight the Matching Answer to the Sum Value

Using conditional formatting in Excel, you can highlight cells that match a specified sum value. Here’s a step-by-step guide to doing this:

1. Choose the set of numbers.

2. Find the Conditional Formatting and click the "New Rule" option.

3. Click " Use a formula to determine which cells to format." and type "=B5=$I$4," then go to format and pick what color you want and finally click "OK."

4. You can change the Name and Day to get the Sum Value without typing the formula of the Sumproduct function again.

The SUMPRODUCT formula is a user-friendly and straightforward way to evaluate product performance in Excel. It allows users to add three or more elements that can affect the value of a product.

The Formulas Tab makes it easy for users to find data on their products and understand what variables impact their success.

When used correctly, there's no better way to figure out product profitability at scale!

1. Go to the "Formulas" Tab in Microsoft Excel.

2. Click Math & Trig and Select the "SUMPRODUCT" formula.

Read Also: Excel Countif Function: Simple Guide For Beginners

Here are a Few Tips on Troubleshooting Errors with SUMPRODUCT

Troubleshooting errors in SUMPRODUCT formulas can be tricky, but don't let it get you down.

Remember to check your brackets; a missing or misplaced bracket could cause the whole formula to fail.

Also, ensure that all relevant arrays are in the same shape and size; for example, if you're trying to sum the products of two arrays, those arrays should have the same number of rows and columns.

Finally, use all array references correctly; a misused individual reference such as A2 instead of A2:A10 can throw off an entire formula.

Final Thoughts on How to Use SUMPRODUCT with Multiple Criteria in Excel 

You have learned the beginner's guide on Sumproduct with multiple criteria in Excel!

Try each procedure out and see which one fits the simplest, and don't forget to check out the rest of the Simple Sheets blog for more great how-to and step-by-step guides!

Frequently Asked Questions About How to Use SUMPRODUCT with Multiple Criteria in Excel: 

What is the SUMPRODUCT function?

The SUMPRODUCT function multiplies and sums the cells or ranges you designate as arguments. This can be helpful when summing up certain types of particulars that first rely upon multiplying two or more parameters.

How do you use the Sumproduct function with multiple criteria?

The SUMPRODUCT function can be used to find the product of two or more ranges and then sum it up. This makes it especially useful when looking simultaneously at multiple factors and particular sets!

By indexing the criteria and using an array formula, you can utilize SUMPRODUCT to quickly identify the sum of the product of cell values in Excel.

Related Articles:

SUM Index-Match: What is it, and How do I use it?

Microsoft Excel Certification: How to Become a Professional

How to Delete Sheets in Excel: Deleting Multiple Sheets at Once