How to use SUMPRODUCT IF to Create a Conditional Formula in Excel

And, one of its powers which I have discovered recently is:


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.

raw data table to apply sumproduct if
  1. 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.
  2. 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.

conditional sumproduct function data table to select pulses

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:


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 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.

three arrays for conditional sumproduct

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.

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.

And for this we have used double minus sign before the first array.

use double minus sigh with sumproduct if statement

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

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.

Sample File


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.

Content Protection by


  1. Kirandeep 16 Nov, 18 at 2:26 pm - Reply

    Nice explanation

  2. Kirandeep 16 Nov, 18 at 2:24 pm - Reply

    Nice explanation,please can you help me to calculate with between dates

  3. Craig 4 Oct, 18 at 2:12 pm - Reply

    Thanks for the example. Is there away to make it even more flexible? In the example above, there is a store id unique product. I want the user to have two drop down boxes where they can select store and/or product. Is there a way build a formula to return a sum if the store or product was left blank? using sumproduct would result in a zero value is one was left blank.

  4. Shedy 26 Sep, 18 at 4:14 pm - Reply

    What if i want to add a list in the dropdown that is “ALL”?

  5. Ambica 19 Jul, 18 at 8:29 am - Reply

    Excellent tips…thanks for sharing us…

    • Puneet 23 Jul, 18 at 7:07 am - Reply

      I’m so glad you liked it. 🙂

  6. Thắng 18 Jul, 18 at 11:28 am - Reply

    Very good solution.
    Thank you!

    • Puneet 19 Jul, 18 at 5:49 am - Reply

      I’m so glad you liked it. 🙂

  7. Yuri 18 Jul, 18 at 10:08 am - Reply

    excellent!!! Really love your post

    • Puneet 18 Jul, 18 at 11:13 am - Reply

      I’m so glad you liked it.

  8. Ahmed Sheikh 4 Nov, 17 at 4:45 pm - Reply

    Hi Puneet!
    I wish you add an option to download the PDF version of discussions for future references.
    Ahmed Sheikh

    • Puneet Gogia 10 Nov, 17 at 8:07 am - Reply

      Didn’t get you, please come again.

      • Ahmed Sheikh 14 Nov, 17 at 7:05 am - Reply

        Well I wanted you to add an option to download the PDF version of your posts/discussions for our future references as in KAWSAR’s discussions.

  9. Vishwanathan Nagarajan 2 Nov, 17 at 6:27 am - Reply

    That is an awesome solution. Thank you so much for the insight and sharing the knowledge 🙂

    • Puneet Gogia 3 Nov, 17 at 5:13 am - Reply

      You’re Welcome

  10. Phil Barker 27 Sep, 17 at 5:24 pm - Reply

    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.

    • Puneet Gogia 28 Oct, 17 at 9:28 am - Reply

      I’m happy you have found this useful.

      • umesan 19 Jul, 18 at 9:13 am - Reply

        Thank You

        • Puneet 23 Jul, 18 at 7:06 am - Reply

          You’re welcome. 🙂

  11. Adrian Palmer 8 Aug, 17 at 3:43 am - Reply

    Hi Puneet. Thanks for the use of the double negative

    • Puneet Gogia 3 Nov, 17 at 5:14 am - Reply

      You’re welcome, Adrian.

  12. Chandeep 9 Feb, 17 at 5:48 pm - Reply

    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 9 Feb, 17 at 6:20 pm - Reply

      Thank You Chandeep for improvement.

      Well, I never thought I can use it.

      Super awesome.

  13. ratanak 24 Oct, 16 at 3:40 am - Reply

    Nice Puneet

    • PG 25 Oct, 16 at 1:26 pm - Reply

      Thanks Ratanak.

    • Puneet Gogia 12 Nov, 16 at 8:22 am - Reply


Leave A Comment