Excel Champs Blog

    Excel Productivity Guide
    Useful Excel Tips | Keyboard Shortcuts | VBA Codes
    Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

    Worth $20, Absolutely Free

    SUMPRODUCT IF to Create a Conditional Formula in Excel

    SUMPRODUCT is one of the most important Excel functions. The best part about it is, 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.

    As you know, you can use sumproduct to multiply and sum specified ranges or arrays.

    While using a conditional sumproduct you can you can multiply and sum those ranges which meet criteria.

    And you know the best part, there is no need to use IF function for this. But first, let me tell you how I got this thing.

    Problem:

    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 functions? I have some data in a table & 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 following components. Download the file from here to follow along.

    1. Stock Data: The table was a stock data which was stored in different stores of her company. It has the product names, units in stock and weight per unit.
    2. Drop Down List: A drop-down menu to select a product.

    Raw Data Table To Apply Sumproduct IF

    From this table, she wanted to get total quantity of a product (units X 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 is a condition here.

    conditional sumproduct function data table to select pulses

    Solution:

    At that time, I was sure about one thing that 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 total of units and quantity per unit only for the cells who meet criteria.

    And, the formula was like this.

    =SUMPRODUCT(- -(C7:C19=C2),E7:E19,F7:F19)

    Solution With Sumproduct If Statment

    Now, when you select an entry from drop down list, this formula will only return the product for cells which meet criteria.

    How Does this Formula Work?

    As you already know that sumproduct function can work with arrays. And, in above method, I have used three arrays to get the product of values.

    three arrays for conditional sumproduct

    The formula works in following way.

    1. Creating a Condition

    First of all, I have created an array to check a condition product name. It will check the values from the product column, return TRUE for the matched values and FALSE for else.

    How SUMPRODUCT IF Statment Works

    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. So, that’s why I have used double minus sign before the first array.

    Use-Double-Minus-Sigh-With-SUMPRODUCT-IF-Statment

    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.

    array working to use condition in sumproduct

    And, when anything multiplies with zero it will return zero. In this way, you will get the product for only those cells where we have 1. In short, where the condition is met.

    Sample File

    download sample file to learn about using conditional in sumproduct

    Conclusion

    The best part about using this conditional sumproduct is you don’t have to use if function and all the calculation is in a single cell.

    As I said sumproduct is one of the most powerful functions and this one best thing you can do with its powers.

    I hope you found it useful. Make sure to share your views in the comment box.

    More About SUMPRODUCT

    • ratanak

      Nice Puneet

      • PG

        Thanks Ratanak.

      • Puneet Gogia

        Thanks

    • The double negative can also be replaced by the N() function. Since in excel 2003 there was nothing to convert TRUE/FALSE to numbers so I used double negative. But a really cool tutorial overall!

      • Puneet Gogia

        Thank You Chandeep for improvement.

        Well, I never thought I can use it.

        Super awesome.

    • Adrian Palmer

      Hi Puneet. Thanks for the use of the double negative

    • Phil Barker

      Very helpful. I found an identical solution elsewhere on a forum, but because it was explained so poorly I didn’t think it was the solution to my problem. Thank you for explaining how this works with a working example. This is exactly what I was looking for.