Why Pro Excel Users Love SUMPRODUCT!
Vložit
- čas přidán 2. 08. 2024
- The Excel SUMPRODUCT function is one of Excel's most versatile and underutilized functions allowing for flexible aggregations of data.
⬇️DOWNLOAD the example file here: www.myonlinetraininghub.com/e...
🎓RELATED LESSON - Database Functions: www.myonlinetraininghub.com/e...
LEARN MORE
===========
🏫 LEARN MORE in my Excel courses: www.myonlinetraininghub.com/
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetraininghub.com/e...
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
📢 Please leave me a COMMENT. I read them all!
🎯 FOLLOW me on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
==============
0:00 SUMPRODUCT Function
0:24 Easy SUMPRODUCT Formula
1:03 SUMPRODUCT with AND Criteria
2:58 SUMPRODUCT with OR Criteria
4:43 SUMPRODUCT with Single Criteria
5:49 SUMPRODUCT to count
6:49 SUMPRODUCT Rules - Věda a technologie
Using the SIGN function trick was a real gem. Thanks!
Glad you can make use of it 👍
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
So pleased you got value from this video 🙏😊
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.
Glad you're inspired to give it a go!
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!
Glad you discovered something useful from my video 🙏😊 I agree, SUMPRODUCT is less important for users with Excel 2021 onward or 365.
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 so much for watching and your support, Roy!
You are a great person. Straight to the point, no blablabla.... Really appreciate your tutorials.
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.
Yes, great points 🙏
This is a FANTASTIC video Mynda !!!! You are Thee Excel Darling!!!
😁Thank you so much!!
So cool Mynda! Thanks a lot.
Glad you liked it! 🙏😊
Long Live SUMPRODUCT. Thank you Mynda!
You bet! 😁
Many thanks, Mynda. Great techniques. The SIGN function is a new one with me!
Great to hear you learned something new, Ian! Thanks for watching 😊
Excellent video. Thanks for such great examples and explanations.
Glad you enjoyed it! 🙏
Thanks Mynda! I use it often for weighted averages
Nice, Chris! Thanks for sharing. 😊
so helpful, Many thanks Mynda.
So glad! 😊
Thank you Mynda for this insightful Video 📹 👏
You are most welcome! 😊
Nice video production Mynda, loved the content too as always :)
Thanks so much!
Another GREAT one. Thanks from South Africa.
Cheers, Chris!
Amazing!
Thank you! Cheers! 😊
Mind Bending is Correct! Another great video.
Thanks so much!
liked it before even watch the video, cause i am confident it just gonna be another great content 👍
You're the best! 😃
UAU!!!!! Many thanks for the content! amazing!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thanks so much! 🙏😁
I am so many thank you, I know more new knowledge.
You are most welcome! 😊
Well explained!
Thank you! 😊
Great video
❤😎
😁🙏👀
Thanks!
Welcome! 😊
Brilliant Mynda
Thanks so much!
Oh boy That - - blow my mind 😮
Awesome to hear! 🙏
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.
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
Great tip on the use of SIGN()
Glad it was helpful!
I also use Sumproduct as an alternative to xlookup or vlookup as the latter returns ref errors if the relevant files are not open
Nice hack! Thanks for sharing.
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??
thanks
You're welcome!
Love you teacher Subscribe is done
Wonderful! 🙏😊
@@MyOnlineTrainingHub make a video on back office job
Awesome
Thank you!
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!!
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.
@@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!!
You're welcome to post your question and sample Excel file on our forum where someone can help you: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub What a great forum. Thanks so much.
I solved the problem by the way with ISNUMBER. Took me a few goes!
Did not know about SIGN , I've always done an OR minus AND ,
COUNTIF(Range1, Criterion1) + COUNTIFS(Range2, Criterion2) - SUM((Range1,Criterion1)*(Range2, Criterion2))
Glad you learnt something new 😊 thanks for watching!
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!
Great point! Thanks for sharing, Duncan.
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
Great to hear! 🙏😊
Omg. Even with explanation i can't understand. Will have to actually try it. I mean the sign function part.
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
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! :-)
PS: if you use the SUM-FILTER combo instead, you don’t need the SIGN function for multiple OR criteria.
@@GeertDelmulle Both of your comments were very helpful. Thanks!
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.
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.
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: czcams.com/users/postUgkxNtZbev1D4JAX-dWlSariFlpYBEey3Vk6
sumproduct = formula array without cntr+shift+enter AVGif = sumproduct/sumproduct
You got it!
Hi Mynda,
How can we evaluate row and column and/or condutions of array by using Sumproduct?
Not sure what you mean by 'evaluate'?
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
I have already replied to you multiple times!
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?
Either the cells compared, or one of the additional criteria is in non compatible form i guess... .
You also forgot the double '--' before the logical test
@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
@@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!
@@ricos1497 is right, i did not receive an error.
İn any case thanks for the replies (:
instead of - - , we could simple use "*1" to force a 0 or 1 value right?
2 =SUMPRODUCT((D7:D10
Yes 👍 any math operation will convert the Boolean values to their numeric equivalent.
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]))
Thanks for watching my trip down memory lane 😁
"Hello [Teacher's ],
I hope you're doing well! I've been thoroughly enjoying your instructional videos on CZcams, 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]"
Thanks for your kind words! It's great to hear you found my videos helpful. I teach those skills in my Power BI and Excel Dashboard courses: www.myonlinetraininghub.com/power-bi-course
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.
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: czcams.com/video/L4BuUzccLpo/video.html
If put inside countif for multiple Or, make magic too
Not following.
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
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.
Absolutely, if you have Excel 2021 or 365, you can do similar things using FILTER. More on FILTER in this video: czcams.com/video/ZCQAweoAdOw/video.html
I haven't speed tested FILTER against SUMPRODUCT, so there may be a use case there.
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 😂.
Thanks for sharing your uses of SUMPRODUCT...and yes, you can do it with SUMIFS too 😁
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 😊)!!!
Wonderful to hear, Jose!
I have never ever heart about this "double unary" --. I always use the multiplication with 1 instead.
Thanks for watching. Glad you discovered something new 😊
Thank you Mynda... And I thought I was an Excel Ninja... :( 😥
Aw, too kind 🙏
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
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.
@@MyOnlineTrainingHub Oh you're right! I was looking to calculate average $ sold, not the average sales price per unit. Thanks again!
SIGN function ! That was my big takeaway. I’ll put it to work immediately.
Awesome to hear!
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.
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
@@MyOnlineTrainingHub Thank you very much for this hint! :) I will certainly try this out.
👍😎✊
🙏😁
an equivalent to the basic SUMPRODUCT iteration: =SUM(C7:C10*D7:D10)
Yes, love that variation too. Thanks for sharing 😊
@@MyOnlineTrainingHub Thanks! I like your discussion of SIGN, which reduces the 1+1 outcome of 2 Boolean TRUES back to 1.
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
Yes, you can reference a cell or another defined name for the level variable 👍