SUMPRODUCT is one of the most important Excel functions. The best part about it is, that you can modify it in several ways.
And one of its powers which I have discovered recently is: Using it as SUMPRODUCT IF In short, a Conditional SUMPRODUCT.
Well, as you know, you can use SUMPRODUCT to multiply and sum specified ranges or arrays. With a conditional SUMPRODUCT, you can multiply and sum those ranges which meet the criteria.
Yes, you heard it right. And you know the best part? There is no need to use the IF function for this. Isn’t it awesome?
So today in this post, I’d like to share with you a problem and a simple way to use SUMPRODUCTIF to solve it.
So be with me to learn one of the most amazing Excel formulas and make sure to download sample files.
How I Got to Know About this Conditional SUMPRODUCT
First thing first.
Yesterday, I got a mail from one of my subscribers. She wanted to create a conditional statement with SUMPRODUCT and IF to get data from a table.
Here is the mail which I have received:
Hey Puneet, I need your help. Is there any way to combine SUMPRODUCT IF? I have some data in a table, and I want to get the product of two columns meeting criteria. Please help me out.
Along with the mail, I got an Excel file from her with the following components. You can download this file from here to follow along.
- Stock Data: This table is stock data that is stored in different stores of her company. It has the product name, units in stock, and weight per unit.
- Drop Down List: A drop-down menu to select a product.
From this table, she wanted to get the total quantity of a product (units * weight per unit) by selecting the dropdown.
For example, if she selects “Pulses”, the total quantity in the cell should be total by multiplying units by the quantity per unit.
Please note down, that a product name is a condition here.
Solution with SUMPRODUCT IF
At that time, I was sure about one thing to get the sum of the product of arrays or ranges we can use SUMPRODUCT.
But here, the game was to get the sum by multiplying the total of units and quantity per unit only for the cells that meet the criteria.
And the formula we can use:
Now, when you select an entry from the drop-down list, this formula will only return the product for cells that meet the criteria.
Hey, wait for a minute: Just like this formula, I have listed a few more smart formulas which can amaze you.
- Conditional Ranking in Excel using SUMPRODUCT Function
- How to use MAX IF Formula in Excel
- How to use OR Logic in COUNTIF/COUNIFS in Excel
- How to use SUMIF / SUMIFS with an OR Logic in Excel
How it Works
As you already know, SUMPRODUCT can work with arrays.
The formula works in the following way.
1. Creating a Condition
First, we have an array to check the condition of the product name. It will check the values from the product column, and return TRUE for the matched values, and FALSE for others.
2. Using Double Minus Sign
Now, the next thing is to convert TRUE-FALSE values into 0-1 so that we can use them in the calculation. And for this, we have used the double minus sign before the first array.
3. Multiplying Arrays
After converting TRUE-FALSE into 0-1 the array will look something like this. Here all the values where criteria are not met, we have zeros.
Remember, when anything multiplies with zero it returns to zero. In this way, we get the product for only those cells where we have 1. In short, where the condition is met.
The best part about using this conditional SUMPRODUCT is you don’t need to use IF and all the calculation is in a single cell. As I said SUMPRODUCT is one of the most powerful functions and this is the best thing you can do with its powers.
Now tell me one thing.
Have you ever tried using a condition in SUMPRODUCT before?
Please share your views with me in the comment section. I’d love to hear from you and please don’t forget to share it with your friends, I am sure they will appreciate it.