Why Pro Excel Users Love SUMPRODUCT!

[@pipo441]

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

[@MyOnlineTrainingHub]

Thanks so much!

[@jonk3361]

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

[@MyOnlineTrainingHub]

So pleased you got value from this video 🙏😊

[@gospelmoto2833]

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.

[@MyOnlineTrainingHub]

Glad you're inspired to give it a go!

[@sachinrv1]

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.

[@MyOnlineTrainingHub]

Yes, great points 🙏

[@hk_200k]

I was dealing with countifs 10+criteria within different spreadsheet and the my colleague's code was longer than a page and unreadable. GPT revised it for me to sumproduct, clean and tidy, but this video tells me the logic behind clearly! Thanks.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@roywilson9580]

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.

[@MyOnlineTrainingHub]

Thanks so much for watching and your support, Roy!

[@chrism9037]

Thanks Mynda! I use it often for weighted averages

[@MyOnlineTrainingHub]

Nice, Chris! Thanks for sharing. 😊

[@spilledgraphics]

Long Live SUMPRODUCT. Thank you Mynda!

[@MyOnlineTrainingHub]

You bet! 😁

[@Quidisi]

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!

[@MyOnlineTrainingHub]

Glad you discovered something useful from my video 🙏😊 I agree, SUMPRODUCT is less important for users with Excel 2021 onward or 365.

[@TakeonmeLiive]

Using the SIGN function trick was a real gem. Thanks!

[@MyOnlineTrainingHub]

Glad you can make use of it 👍

[@aiasaiascon3894]

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

[@MyOnlineTrainingHub]

😁Thank you so much!!

[@jdp5288]

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

[@MyOnlineTrainingHub]

Nice hack! Thanks for sharing.

[@danteencinas7020]

Thank you for this wonderful explanation of the SUMPRODUCT function!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@iankr]

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

[@MyOnlineTrainingHub]

Great to hear you learned something new, Ian! Thanks for watching 😊

[@hayekfriedman9078]

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

[@MyOnlineTrainingHub]

hmm, the SPILL! error means there's data in cells the formula wants to spill to. You need to delete that data for the formula to return the results.

[@hayekfriedman9078]

@@MyOnlineTrainingHub Thank you for your response. I keep seeing formulas on various websites that tells sumproduct to exclude text results from am aray it just never works for me! Excel is evil and HATES me!!

[@MyOnlineTrainingHub]

You're welcome to post your question and sample Excel file on our forum where someone can help you: www.myonlinetraininghub.com/excel-forum

[@hayekfriedman9078]

@@MyOnlineTrainingHub What a great forum. Thanks so much. I solved the problem by the way with ISNUMBER. Took me a few goes!

[@williamarthur4801]

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

[@MyOnlineTrainingHub]

Glad you learnt something new 😊 thanks for watching!

[@mac2375]

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

[@MyOnlineTrainingHub]

Thanks so much!

[@AcukOndanAcukBundan]

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.

[@MyOnlineTrainingHub]

Thanks so much for your feedback! I have actually started on the quest for a designer and just ran a survey to help choose which thumbnail for an upcoming video. You can vote here: kzbin.infoUgkxNtZbev1D4JAX-dWlSariFlpYBEey3Vk6

[@s.y.daniel2137]

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

[@MyOnlineTrainingHub]

Yes 👍 any math operation will convert the Boolean values to their numeric equivalent.

[@artil1]

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.

[@MyOnlineTrainingHub]

Good point. Yes, for those with Dynamic Arrays. But for users on earlier versions of Excel, SUM would require entering with CTRL+SHIFT+ENTER to handle array inputs (from memory). The multiplication of logical arrays inside SUM is coercing the Boolean values to their numeric equivalents. You could do this in SUMPRODUCT too by replacing the comma with multiply e.g. SUMPRODUCT(array1* array2) If you have DAs, then SUM would win because it's 7 less characters in your formula 😁 Keep your eye out for my video on the 17th October as I talk about logic

[@michaelkuhn6328]

Excellent video. Thanks for such great examples and explanations.

[@MyOnlineTrainingHub]

Glad you enjoyed it! 🙏

[@ousmanesidibe951]

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

[@MyOnlineTrainingHub]

You're the best! 😃

[@ArchanaNayak-u3f]

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

[@willw8896]

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.

[@MyOnlineTrainingHub]

Thanks for watching and your questions. SUMPRODUCT can be resource intensive across large datasets when used with a lot of logical tests. If it's also referencing external workbooks, then that will be a killer. I would use Power Query to reference the data in external files and bring it into the current file, rather than use formulas. More on Power Query here: kzbin.info/www/bejne/gmWlpoiwmMh_ptE

[@teoxengineer]

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

[@MyOnlineTrainingHub]

Not sure what you mean by 'evaluate'?

[@ExcelWithChris]

Another GREAT one. Thanks from South Africa.

[@MyOnlineTrainingHub]

Cheers, Chris!

[@otskhan]

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?

[@keylanoslokj1806]

Either the cells compared, or one of the additional criteria is in non compatible form i guess... .

[@keylanoslokj1806]

You also forgot the double '--' before the logical test

[@MyOnlineTrainingHub]

@otskhan, great to hear. You can coerce the logical test my multiplying the arrays if you prefer. I'd say @keylanoslokj1804 is right in that the error will be not compatible. Hard to say without knowing the error and seeing the data. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@ricos1497]

​​@@MyOnlineTrainingHubI don't think the OP is getting an error, they were asking if the way that they'd set out the problem (dropping the -- and simply using multiplication) could lead to an error. I think! For what it's worth, I think that writing has the OP has is fine. It's pretty much the syntax you'd use in the filter function. Edit: great video though!

[@otskhan]

@@ricos1497 is right, i did not receive an error. İn any case thanks for the replies (:

[@AhmedAbdalalim]

so helpful, Many thanks Mynda.

[@MyOnlineTrainingHub]

So glad! 😊

[@khanabdussabur8604]

So cool Mynda! Thanks a lot.

[@MyOnlineTrainingHub]

Glad you liked it! 🙏😊

[@HandelMcHandel]

Mind Bending is Correct! Another great video.

[@MyOnlineTrainingHub]

Thanks so much!

[@aperson2020]

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

[@MyOnlineTrainingHub]

Yes, please download the example file and play with the SIGN function. If you have any questions, reach out via our forum: www.myonlinetraininghub.com/excel-forum

[@nadermounir8228]

Thank you Mynda for this insightful Video 📹 👏

[@MyOnlineTrainingHub]

You are most welcome! 😊

[@GeertDelmulle]

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! :-)

[@GeertDelmulle]

PS: if you use the SUM-FILTER combo instead, you don’t need the SIGN function for multiple OR criteria.

[@Quidisi]

@@GeertDelmulle Both of your comments were very helpful. Thanks!

[@MyOnlineTrainingHub]

Thanks for watching, Geert! Yes, if you're on Excel 2021 or 365, and don't need to worry about backward compatibility, FILTER is a great alternative 🙏thanks for sharing.

[@sunnybaggu785]

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

[@Quidisi]

I am on a quest to discover if, assuming Office 365, there is any need for SUMPRODUCT. I've yet to discover one. Maybe it's faster?? Anyway, if anyone knows of any reason why SUMPRODUCT is still needed, please let me know. Thanks.

[@MyOnlineTrainingHub]

Absolutely, if you have Excel 2021 or 365, you can do similar things using FILTER. More on FILTER in this video: kzbin.info/www/bejne/kHS0cqqbpKaXhdk I haven't speed tested FILTER against SUMPRODUCT, so there may be a use case there.

[@goutamnayak5011]

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

[@MyOnlineTrainingHub]

I have already replied to you multiple times!

[@christoslefkimiotis9889]

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

[@MyOnlineTrainingHub]

You got it!

[@duncanwil]

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!

[@MyOnlineTrainingHub]

Great point! Thanks for sharing, Duncan.

[@byambatsogtchuluunbaatar4899]

Brilliant Mynda

[@MyOnlineTrainingHub]

Thanks so much!

[@OneRouteUK]

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]))

[@MyOnlineTrainingHub]

Thanks for watching my trip down memory lane 😁

[@WebberJason]

Great tip on the use of SIGN()

[@MyOnlineTrainingHub]

Glad it was helpful!

[@panksimus]

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

[@MyOnlineTrainingHub]

Thanks for sharing your uses of SUMPRODUCT...and yes, you can do it with SUMIFS too 😁

[@IamTheReaper911]

Great video ❤😎

[@MyOnlineTrainingHub]

😁🙏👀

[@luigibru8677]

Well explained!

[@MyOnlineTrainingHub]

Thank you! 😊

[@ketanj2890]

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.

[@MyOnlineTrainingHub]

You can use VLOOKUP or XLOOKUP for that. You need the 'approximate match' version of these formulas. Here is XLOOKUP: www.myonlinetraininghub.com/excel-xlookup-function#10 Here is VLOOKUP: www.myonlinetraininghub.com/excel-vlookup-sorted-list-explained If you get stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@ketanj2890]

@@MyOnlineTrainingHub Thank you very much for this hint! :) I will certainly try this out.

[@plfernandes22]

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

[@MyOnlineTrainingHub]

Thanks so much! 🙏😁

[@lopher70]

Oh boy That - - blow my mind 😮

[@MyOnlineTrainingHub]

Awesome to hear! 🙏

[@PhuongNguyen-sf3yc]

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

[@MyOnlineTrainingHub]

You are most welcome! 😊

[@manuelschmidt4747]

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

[@MyOnlineTrainingHub]

Thanks for watching. Glad you discovered something new 😊

[@ahrorkuldashev9603]

Amazing!

[@MyOnlineTrainingHub]

Thank you! Cheers! 😊

[@jeffstevens9729]

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

[@MyOnlineTrainingHub]

Glad you discovered something new! Your formula is almost there. It should be: =SUMPRODUCT(--(C7:C10>100),C7:C10,D7:D10)/SUMPRODUCT(--(C7:C10>100),C7:C10) You just missed the units sold range in the denominator.

[@jeffstevens9729]

@@MyOnlineTrainingHub Oh you're right! I was looking to calculate average $ sold, not the average sales price per unit. Thanks again!

[@josecarlosconejo5724]

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 😊)!!!

[@MyOnlineTrainingHub]

Wonderful to hear, Jose!

[@danielgoncalveslima9350]

If put inside countif for multiple Or, make magic too

[@MyOnlineTrainingHub]

Not following.

[@mogarrett3045]

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

[@MyOnlineTrainingHub]

Great to hear! 🙏😊

[@muaawais3316]

Love you teacher Subscribe is done

[@MyOnlineTrainingHub]

Wonderful! 🙏😊

[@muaawais3316]

@@MyOnlineTrainingHub make a video on back office job

[@mathijs9365]

Thanks!

[@MyOnlineTrainingHub]

Welcome! 😊

[@neelk9807]

thanks

[@MyOnlineTrainingHub]

You're welcome!

[@signalmas5673]

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

[@MyOnlineTrainingHub]

Awesome to hear!

[@Erni760803]

Awesome

[@MyOnlineTrainingHub]

Thank you!

[@carlo_migliari81]

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

[@MyOnlineTrainingHub]

Yes, you can reference a cell or another defined name for the level variable 👍

[@Hello-bn2yc]

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

[@MyOnlineTrainingHub]

Aw, too kind 🙏

[@abbottkatz8830]

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

[@MyOnlineTrainingHub]

Yes, love that variation too. Thanks for sharing 😊

[@abbottkatz8830]

@@MyOnlineTrainingHub Thanks! I like your discussion of SIGN, which reduces the 1+1 outcome of 2 Boolean TRUES back to 1.

[@steven.h0629]

👍😎✊

[@MyOnlineTrainingHub]

🙏😁