Why Pro Excel Users Love SUMPRODUCT! 

This is priceless! I am amazed by how simple you make it look. You're truly an excel legend. I will definitely watch it over and over again especially to understand how the sign works. Many thanks Mynda

Long Live SUMPRODUCT. Thank you Mynda!

You are a great person. Straight to the point, no blablabla.... Really appreciate your tutorials.

Learned something new today. I've heard sumproduct before but I am not using it. Now, I'm inspired to use it. Thanks for your video once again. You're getting lovelier and lovelier each time I watch you.

This is a FANTASTIC video Mynda !!!! You are Thee Excel Darling!!!

SIGN() is my new favorite function. Amazing. I always struggled with how to reduce my array result to either 0 or 1. Since Excel now handles arrays natively - I still don't see any reason to use SUMPRODUCT vs just summing your array calculations. For example, this =SUMPRODUCT((C6:C9>85)*(D6:D9>15),C6:C9,D6:D9 is equal to this =SUM((C6:C9>85)*(D6:D9>15)*C6:C9*D6:D9) If anyone knows an example where SUMPRODUCT must be used, please share! Mynda - loving SIGN(). I also tried it, and you can use it in lieu of double minus signs. I think I will just start using SIGN to be consistent. Thanks so much!

SUMPRODUCT is amazing, as it can take care of AND, OR and combination of AND OR conditions. Thanks for the useful video. The most important feature of this function is that it can take care of ARRAY operations, so those who don't have Office 365, this function is super useful.

so helpful, Many thanks Mynda.

Many thanks, Mynda. Great techniques. The SIGN function is a new one with me!

SUMPRODUCT(array1, array2) is actually SUM(array1 * array2). More to say, SUMPRODUCT needs conversion of boolean type, while conversion in SUM function is done implicitly by multiplication. So SUMPRODUCT is actually redundant.

So cool Mynda! Thanks a lot.

Excellent video. Thanks for such great examples and explanations.

Sumproduct has so many uses, I still find myself reaching for it in preference to the newer functions. It is extremely useful and should, I think, be part of all Excel courses from the intermediate level up. Thanks for bringing it to the attention of some newer users who may not have heard of it's extended uses.

Thanks Mynda! I use it often for weighted averages

Thank you Mynda for this insightful Video 📹 👏

Another GREAT one. Thanks from South Africa.

Nice video production Mynda, loved the content too as always :)

Brilliant Mynda

liked it before even watch the video, cause i am confident it just gonna be another great content 👍

Mind Bending is Correct! Another great video.

Amazing!

I am so many thank you, I know more new knowledge.

UAU!!!!! Many thanks for the content! amazing!!!!!!!!!!!!!!!!!!!!!!!!!!!

Well explained!

Thanks!

Omg. Even with explanation i can't understand. Will have to actually try it. I mean the sign function part.

Oh boy That - - blow my mind 😮

Great video ❤😎

Great tip on the use of SIGN()

I also use Sumproduct as an alternative to xlookup or vlookup as the latter returns ref errors if the relevant files are not open

thanks

Very nice video Mynda i have doubt i am a beginner there lot of ai tools in market so is it still important to develop skills like power bi ,ms excel by myself??

Did not know about SIGN , I've always done an OR minus AND , COUNTIF(Range1, Criterion1) + COUNTIFS(Range2, Criterion2) - SUM((Range1,Criterion1)*(Range2, Criterion2))

Your Mynda videos are really nice, but I can't say that the thumbnails of your videos are very good. I have been watching you for a long time and I like your videos. I think you can find a designer.

Awesome

Love you teacher Subscribe is done

excellent tutorial as always. I sometimes struggle when going between both excel formulas vs DAX code , but you always seem to me there. thank you Mo

Another excellent video. What I have never been able to make work is using sumproduct with iferror to exclude text values within an array. I only ever end up with SPILL!!

sumproduct = formula array without cntr+shift+enter AVGif = sumproduct/sumproduct

I was just about to say, aha, database functions can do some or all of this but you beat me to it. There is another feature of the database functions that I think is a real winner, which is that the formulas themselves are essentially static in terms of their size: that is number of characters while the SUMIFS, SUMPRODUCT etc functions can become really unwieldy. Still, a good video, of course!

Hi Mynda, How can we evaluate row and column and/or condutions of array by using Sumproduct?

instead of - - , we could simple use "*1" to force a 0 or 1 value right? 2 =SUMPRODUCT((D7:D10

That last statement is indeed an important one: not computationally the most efficient way, the DAX adage holds true: it is better to filter first and then calculate instead of the other way round, and that’s where SUMPRODUCT does it in the wrong order. Good, concise video, BTW, kudos! :-)

Great video Mam can copilot 365 and other ai tools capable of doing Advance Excel functions? If it is capable then still it is important of learning advance functions ms Excel??? Please reply me mam

Ive all but forgotten how to use SUMPRODUCT since the ‘IFS’ functions, however I do use it for OR functions when there’s multiple criteria that I have in a list within one range. =SUMPRODUCT(SUMIF([criteria range],[criteria list],[sum range]))

If put inside countif for multiple Or, make magic too

Hi Mynda, I really like the robustness of SUMPRODUCT but there have been some instances with some models where I introduced SUMPRODUCT where the calculation speed was very slow, locking up recalculations for minutes. We had to turn Automatic calculation to Manual calculation and use F9 and Shift+F9 (refresh workbook, refresh sheet) often. Would you be able to comment on what can cause the calculation speed to be very slow? E.g., does it get slow by using an excessively large lookup range, using SUMPRODUCTs that look into externally linked files, etc? And, tips on how to avoid this? For context, I typically have a plug-in installed like TM1 Planning Analytics, SAP Analysis, HFM, etc. I mention that in case you might think that these plug-ins interfere with SUMPRODUCT calculation speed.

First of all, I would like to state that I have learnt dozens of things from your videos, so thank you very much. Especially for single criteria, I've been using * (multiply) rather than -- (double minus) and for both single and multiple criteria i use multiplication and not using additional arrays as formula suggests. What i mean is that i use this version in your single criteria example =SUMPRODUCT((B36:B39>DATE(2023,09,02))*(D36:D39)*(E36:E39)) I wonder if there's anything I'm missing, that could lead an error?

I have never ever heart about this "double unary" --. I always use the multiplication with 1 instead.

I think you don't need sumproduct in latest version of excel, guess even sum when placed in sumproduct will yield the same result provided with proper usage of operators

It was with SUMPRODUCT how understood how Excel works. Since that day my learning curve became exponential and Excel not only a tool anymore but a great passion. Long Live SUMPRODUCT (and Excel of course 😊)!!!

SIGN function ! That was my big takeaway. I’ll put it to work immediately.

"Hello [Teacher's ], I hope you're doing well! I've been thoroughly enjoying your instructional videos on RU-vid, and I've learned so much from your expertise. Your teaching style is both informative and engaging. I wanted to kindly request if you could create some tutorial videos on Excel and Power BI dashboard reports for sales. Many of us here in the community would greatly benefit from your guidance in these areas. Your knack for simplifying complex topics and breaking them down into easy-to-follow steps is truly appreciated. Please consider our request, and if it's possible, could you create a series of videos covering Excel techniques for data analysis and visualization, as well as a comprehensive guide on how to create impactful sales reports using Power BI? I'm sure your insights and tips would be invaluable to all of us striving to excel in these skills. Thank you for your dedication to educating and empowering your viewers. Looking forward to more fantastic content from you! Warm regards, [castlpy]"

I use "sumproduct" mainly to lookup and bring in numbers based on multiple criteria. Where source has multiple rows and columns headers to lookup from. Can this be done with "sumifs"? Probably yes 😂.

My idea concerning averageifs: let's suppose i want the average Total Sales for prices under 20; I've used the same table but starts in a different location; LET( level;20; prcs;AC5:AC8; untsld;AB5:AB8; calc;prcs

Great video. Been using SUMPRODUCT for years but using SIGN and "--" have escaped my attention. Btw I think the calculation for AVERAGEIFS should be =SUMPRODUCT(--(C7:C10>100),C7:C10,D7:D10)/SUMPRODUCT(--(C7:C10>100)). That way the denominator only counts the units over 100

Is it possible to use SUMPRODUCT for a problem describes below? I've got two tables as below: Table1 has values in one column A: A 1 2 4 8 10 12 14 18 20 22 24 26 28 30 Table2 has pair of numbers in each row, represented as lower and upper limits in column A and B respectively as shown below: A B 2 6 9 11 14 16 19 21 24 26 29 31 I'd like to populate column B of Table1 for each corresponding value in column A with result as 1 or 0 where , 1 means that the number is available between a pair of numbers given in Table2, and 0 means it isn't available between a pair of numbers given in Table2.

Thank you Mynda... And I thought I was an Excel Ninja... :( 😥

👍😎✊

an equivalent to the basic SUMPRODUCT iteration: =SUM(C7:C10*D7:D10)