How to Use SUMPRODUCT in Excel

Quickly learn how Excel's SUMPRODUCT function works. It is one of Microsoft Excel's most powerful yet often overlooked function.

🔑 Key Highlights:
▪️ Start with basic SUMPRODUCT uses - perfect for those new to Excel.
▪️ Advanced Applications: See how SUMPRODUCT can replace complex functions like VLOOKUP and SUMIFS.
▪️ Real-World Examples: From calculating revenue to performing OR conditions, learn through practical scenarios.
▪️ Pro Tips: Crucial syntax tips for error-free usage.

The classical use of Excel's SUMPRODUCT function is to do a sum of multiplications. The good thing about SUMPRODUCT is that it can work on arrays of values and it doesn’t have to be entered as an array function so no need to do control + shift + enter (CSE).

The great thing about SUMPRODUCT, is that it can help solve many more problems in non-obvious ways. It can act as a conditional SUMIFS function or work as a lookup function as INDEX and MATCH does. It can also act as a COUNTIF function.

SUMPRODUCT is also a better SUMIFS, because it can check for OR conditions - which the SUMIFS can't. SUMIFS can only check for AND conditions.

In this video you will see different examples of the SUMPRODUCT function and you will learn how it can save you from unnecessary "workarounds" in your Excel files. It will also show you the SUMPRODUCT formula in slow motion.

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[smithanimal]

I can't believe how helpful this video was. As someone somewhat advanced in Excel the concept of SUMPRODUCT's utility has always somewhat evaded me. This video was laid out in such a perfect and simple format I completely grasp how the command is actually calculating now. I'm going to turn to your channel for any excel help. Nicely done!

[robertofelce]

The hot tip for the + meaning or, blew my mind. Didn't know about it. Thank you so much!

[frankie]

Superb. Without a doubt, you're excel guru. Someone called me yesterday guru because I made for them a simple software. But you're the real guru you're teaching billions of people around the world.

[lpanebr]

This is awesome. Everytime I think "oh, I already know about this function" I'm wrong, because there is always something new to learn with your videos. Thank you!

[kapilprasannaa]

This helped me so much in my Excel interview today. I used sumproduct instead of countifs and sumifs and the interviewer was visibly impressed.
Thank you so much - you will have a lot of good karma (and business) for doing this.

[kaigeertsma]

I spend hours to build a formula of index, match, countif and sum to find the sum value of a customer, But after watching this video for 3 minutes I see that SUMPRODUCT does exactly what I want lol. Thanks Leila Gharani, your tutorials are amazing!

[jimbarton]

One of your very best videos! Not only did I learn something about SUMPRODUCT, but your "slow motion" explanation is elegant and understandable. I try something similar when teaching SUMIFS, but you've really clarified it.

[shaolinmaster]

I love Leila!!! My favorite excel teacher!! I recommend her to everyone who wishes to expand their knowledge about any aspect of this tool!!!

[TJMelrose]

I wish I could give this video 1000 thumbs-up. I finally understand SUMPRODUCT well enough to use it on my own. Thank you Leila Gharani!

[sachinrv]

Hi Leila, you have made life so easy for all of us. The only thing one has to do is turn on YouTube and watch your channel. You have so precisely explained use of comma and aster sign while using SUMPRODUCT.

[rauljimenez]

The simplicity and elegance of this use of sumproduct is genious, you rock!! 😀

[williamarthur]

I've always loved Sumproduct, it does so much; and nice to hear / see someone emphasizing the importance of bracketing, I ended up creating a crib sheet of every AND / OR combination I could think as I was always getting confused. Still do for that matter.

[hadibq]

Great demo for this great function! the array aspect of it now got a bit forgotten by old-timers with all the new array native functions. TY

[williamedgarperrigo]

i’ve worked with sumproduct but this segment cleared up unanswered questions. Thank you!

[CheerfulMillie]

Super clear demonstration. Short and precise. Thank you!

[sunnyn]

Hello!! This is an amazing channel! I used Sumifs, index match mainly but not Sum product. I knew more than most in both organisations over the past decade but, this is a game changer! Thank you so much!

[EliotPowell]

First, I have only recently discovered your Youtube channel, and I find it to be super helpful, and secondly, your explanation and uses for SUMPRODUCT will absolutely change the way I use Excel.

A few comments:

1. Typos in the in Cells H23, H24 and H25 the word is SUMPRODUCT (not “SUMPRODCUT”)

2. In practicality, I find the Average Price calculation misleading. Revenue of 98, 200 divided by a quantity of 470 units is 208.94. Your calculation of 208.40 is effectively a simple average of the 5 prices, not reflective of how much was sold.

3. Curiously, when you double check the Average Price (around 13:45 of the video), the Status Bar shows an average value for the highlighted values in cells D46, D47, D48, D50 and D52 of 208. I get the same result of 208 when highlighting the numbers in the same sequence (D46, D47, D48, D50, then D52)


4. The numbers in those cells (100, 200, 320, 222, and 200) actually have an average of 208.4. Somehow, the Status Bar shows a different (incorrect?) number of 208.

5. Even more curious, on my computer, when I highlight the cells from your file in reverse order (D52, D50, D48, D47, then D46) the Status Bar shows the correct Average Price of 208.4 … I don’t know why this is, but I am hoping you can explain it!

[DouDouMa]

I have been using excel for work for quite sometime. Have been searching what function can perform both sumif & sumproduct at the same time. Finally, found this video! This is the most powerful function I have learnt in Excel so far! The tutorial had explained the function very clearly that make very easy to understand & apply! Also found that besides "+", can also use "-" to exclude some conditions. Thanks a lot!

[shironmania]

Rightly said that this is for advanced users....many would have used sumproduct in a elementary level but with criteria it makes a very useful tool....thanks again

[snicho]

This just became my favourite explanation of the SUMPRODUCT function!
All of the blog posts about SUMPRODUCT that I had previously bookmarked are now redundant.
Great job Leila!