Pinterest Pixel

How to Use SUMPRODUCT with IF in Excel – 3 Perfect Examples

Microsoft Excel is a popular spreadsheet software that offers a vast array of functions and formulas that... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to Use SUMPRODUCT with IF in Excel – 3 Perfect Examples | MyExcelOnline How to Use SUMPRODUCT with IF in Excel – 3 Perfect Examples | MyExcelOnline

Microsoft Excel is a popular spreadsheet software that offers a vast array of functions and formulas that can assist in complex data analysis.

Among these, the SUMPRODUCT function stands out as a versatile tool, allowing users to perform calculations on multiple ranges of data simultaneously.

SUMPRODUCT with IF takes data analysis to a whole new level by incorporating boolean logic and conditional calculations.

In this article, we will explore the ins and outs of using SUMPRODUCT with the IF function, providing comprehensive examples and explanations to help you harness its full potential.

Let us look at each of these points one by one!

Make sure to download this Excel Workbook and follow along to understand how to use SUMPRODUCT with IF in Excel:

download excel workbookSUMPRODUCT-IF.xlsx

Want to How to Use SUMPRODUCT in Excel?

*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***

Watch on YouTube and give it a thumbs up 👍

How to Use SUMPRODUCT with IF in Excel – 3 Perfect Examples | MyExcelOnline

 

Syntax

SUMPRODUCT

=SUMPRODUCT(array1, [array2], [array3], …)

The SUMPRODUCT function syntax has the following arguments:

  • array1 – The first array argument whose components you want to multiply and then add. (Required)
  • [array2], [array3], … – Array arguments 2 to 255 whose components you want to multiply and then add. (Optional)

 

IF

=IF(logical_test, value_if_true, [value_if_false])

The IF function syntax has the following arguments:

  • logical_test – The condition you want to test. (Required)
  • value_if_true – The value that you want to be returned if the result of logical_test is TRUE. (Required)
  • value_if_false – The value that you want to be returned if the result of logical_test is FALSE. (Optional)

Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it’s false.

See also  How to Check If Cell Contains Text with Excel Formulas

 

Examples of SUMPRODUCT with IF – Example 1

To better grasp the concept of Sumproduct, let’s begin with a simple example. Consider a dataset containing sales quantities and prices for different products:

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

If we want to calculate the total sales value, we can use Sumproduct as follows:

STEP 1: Enter the formula.

=SUMPRODUCT(

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

STEP 2: Enter the first argument, which is the first array that we want to sum and multiply.

=SUMPRODUCT(B2:B5,

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

STEP 3: Enter the second argument, which is the second array that we want to sum and multiply.

=SUMPRODUCT(B2:B5,C2:C5)

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

Let’s see what the end result looks like.

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

As you can see, the formula multiplies the corresponding values in each array we selected and then adds up all the values obtained. This gives us the final sales value of our example.

Before we proceed with our next example, let’s understand a new concept – Boolean Logic!

In Excel, Boolean logic is the use of logical operators to assess and manipulate data based on binary conditions such as TRUE or FALSE. Excel includes a number of logical operators, such as AND, OR, and NOT, that allow users to construct sophisticated logical statements. To execute conditional computations and logical tests, Boolean logic is frequently used in conjunction with other Excel functions, such as the IF function.

Users can make decisions, filter data, perform sophisticated computations, and generate dynamic reports based on specific situations by inserting boolean logic into formulas.

 

Examples of SUMPRODUCT with IF – Example 2

Let us proceed with our previous example for better clarity. Here we want to find the total sales amount of all products where the units sold is at least 10 units.

In this scenario, we can use Sumproduct with the IF function to apply the condition. Let’s break it down.

STEP 1: Enter the SUMPRODUCT formula.

=SUMPRODUCT(

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

STEP 2: Enter the IF function.

=SUMPRODUCT(IF(

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

STEP 3: Enter the criteria for the IF function. We want to check only the values that are above or equal to 10 units in the array B2:B5.

See also  Best Tips & Tricks on Using Nested IF Statement in Excel

=SUMPRODUCT(IF(B2:B5>=10,

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

STEP 4: Now we will enter the value we want if our criteria is met. As we want the values of units sold only, we will enter the array containing the units i.e. B2:B5.

=SUMPRODUCT(IF(B2:B5>=10,B2:B5,

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

STEP 5: Now we will enter the value we want if our criteria is not met. Here, it is 0.

=SUMPRODUCT(IF(B2:B5>=10,B2:B5,0),

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

STEP 6: We now need to select our second array for the sumproduct function which is C2:C5.

=SUMPRODUCT(IF(B2:B5>=10,B2:B5,0),C2:C5)

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

Let’s understand the IF part of our formula a bit more.

  • The IF function checks if the quantity sold in each row (B2:B5) is greater than or equal to 10.
  • If the condition is met, it returns the quantity sold; otherwise, it returns 0.
  • Sumproduct then multiplies the resulting array with the price array (C2:C5) and sums the products.

As a result, we get the total sales amount of all products where the units sold are at least 10 units.

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

 

Examples of SUMPRODUCT with IF – Example 3

Now let us try a more complex example with multiple criteria. Here, we will be using the boolena logi instead of the IF function.

Suppose we want to calculate the total sales value for products sold in the North region during Quarter 1. We can utilize Sumproduct with the IF function to apply multiple conditions simultaneously.

The formula would be:
=SUMPRODUCT((Region=”North”)*(Quarter=”Q1″)*(Quantity Sold), Price)

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

STEP 1: Enter the sumproduct formula.

=SUMPRODUCT(

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

Now we are going to simplify our formula by creating a single array. We are going to simultaneously check our criteria.

STEP 2: Let’s start with our first criteria, which is checking for sales in the North region within our array of B2:B6.

=SUMPRODUCT((B2:B6=”North”)

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

Now we know that the boolean logic changes values to TRUE and FALSE, or it can be seen as 1 and 0 respectively. Hence all values in our array that meet the criteria will be turned to 1, and those that do not meet the criteria will be turned to 0.

See also  Excel ISNA Function: Best Guide to Flawless Formulas

STEP 3: Now we are going to multiply this criteria array with our second criteria array, so we are entering the * operator.

=SUMPRODUCT((B2:B6=”North”)*

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

STEP 4:Similarly, we will now enter our second criteria, which is searching for Q1 in the array C2:C6.

=SUMPRODUCT((B2:B6=”North”)*(C2:C6=”Q1″)

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

STEP 5: This entire argument that we have entered will count as a single argument for the sumproduct function. We will have an array of 5 rows and 1 column consisting of 1s and 0s.

1 where all of our multiple criteria meet, and 0 where at least one criteria do not meet.

Now we need to enter our second argument, which is the first array that we want to sum and multiply, which is the array containing the quantity sold.

=SUMPRODUCT((B2:B6=”North”)*(C2:C6=”Q1″),D2:D6,

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

Now we need to enter our second argument, which is the second array that we want to sum and multiply, which is the array containing the price of the products.

=SUMPRODUCT((B2:B6=”North”)*(C2:C6=”Q1″),D2:D6,E2:E6)

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

As we can see, the result of our formula gives us the total sales value for products sold in the North region during Quarter 1.

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

 

Additional Uses for Sumproduct with IF

The combination of Sumproduct with the IF function opens up a world of possibilities for advanced data analysis in Excel. Here are a few more applications:

1. Weighted Sum:

Sumproduct with IF can be used to assign varying weights to each element in a range before summing them up. For example, if you have stock data with weights and returns, you can calculate the weighted return by multiplying each item by its weight and then summing the products.

In this example, we want to calculate the weighted return of the portfolio but we only want to include stocks with weights greater than 15%.

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

 

2. Data Filtering:

Using Sumproduct with IF, you can filter and extract certain data from a dataset based on conditional criteria. You can retrieve precise subsets of data that fit your criteria by combining various conditions.

See also  IF Function: Introduction

In this example, we are using SUMPRODUCT with IF to get the sales amount when the three criteria are met – Region is North, Sales Rep is John and Quarter is 1.

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

 

3. Performance Evaluation:

Assume you have a dataset with student scores in various subjects and you want to calculate the average grade for each student while only taking into account areas where they scored above a certain threshold. Sumproduct with IF allows you to include or omit subjects depending on defined criteria and calculate the average grade accordingly.

In this example, we want to calculate the total score of the student for all subjects falling under Group A.

How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples

 

Conclusion

When paired with the IF function, Excel’s Sumproduct function enables users to do advanced data analysis and conditional computations effectively. You may unlock the full power of Sumproduct and derive important insights from your datasets by implementing boolean logic and applying numerous conditions.

Sumproduct with IF provides a robust toolkit for solving difficult data analysis jobs in Excel, whether you need to do weighted calculations, filter data, or analyze performance. So go in, play with different scenarios, and unleash the entire power of Sumproduct with IF to take your data analysis talents to the next level.

If you like this Excel tip, please share it
How to Use SUMPRODUCT with IF in Excel – 3 Perfect Examples | MyExcelOnline How to Use SUMPRODUCT with IF in Excel – 3 Perfect Examples | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...