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 criteria.
Yes, you heard it right.
And you know the best part?
There is no need to use IF function for this.
Isn't it awesome?
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.
- Stock Data: This table is a stock data which 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 total quantity of a product (units * weight per unit) by selecting the drop down.
For example, if she selects “Pulses”, the total quantity in the cell should be total by multiplying units with quantity per unit.
Please note down, a product name is a condition here.
Solution with SUMPRODUCT IF
But here, the game was to get the sum by multiplying total of units and quantity per unit only for the cells who meet criteria.
And, the formula we can use it:
Now, when you select an entry from drop down list, this formula will only return the product for cells which meet criteria.
...here how it works
As you already know that SUMPRODUCT can work with arrays.
So in above method, we have used three arrays to get the product of values.
The formula works in following way.
1. Creating a Condition
First of all, we have an array to check a condition for product name.
It will check the values from the product column, return TRUE for the matched values and FALSE for else.
2. Using Double Minus Sign
Now, 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 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 is not met, we have zeros.
Remember, when anything multiplies with zero it returns 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.
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.
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.