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!
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
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.
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.
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.
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
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.
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: ua-cam.com/users/postUgkxNtZbev1D4JAX-dWlSariFlpYBEey3Vk6
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 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!!
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! :-)
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.
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!
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 😊)!!!
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]))
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: ua-cam.com/video/ZCQAweoAdOw/v-deo.html I haven't speed tested FILTER against SUMPRODUCT, so there may be a use case there.
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?
@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!
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??
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.
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: ua-cam.com/video/L4BuUzccLpo/v-deo.html
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 😂.
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
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
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
You are a great person. Straight to the point, no blablabla.... Really appreciate your tutorials.
Thanks so much!
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.
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 🙏😊
Using the SIGN function trick was a real gem. Thanks!
Glad you can make use of it 👍
Thank you for this wonderful explanation of the SUMPRODUCT function!
Glad it was helpful!
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 🙏
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!
Long Live SUMPRODUCT. Thank you Mynda!
You bet! 😁
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!
Thanks Mynda! I use it often for weighted averages
Nice, Chris! Thanks for sharing. 😊
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 😊
This is a FANTASTIC video Mynda !!!! You are Thee Excel Darling!!!
😁Thank you so much!!
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
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!
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: ua-cam.com/users/postUgkxNtZbev1D4JAX-dWlSariFlpYBEey3Vk6
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!
liked it before even watch the video, cause i am confident it just gonna be another great content 👍
You're the best! 😃
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
Another GREAT one. Thanks from South Africa.
Cheers, Chris!
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.
Nice video production Mynda, loved the content too as always :)
Thanks so much!
Excellent video. Thanks for such great examples and explanations.
Glad you enjoyed it! 🙏
so helpful, Many thanks Mynda.
So glad! 😊
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.
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.
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.
So cool Mynda! Thanks a lot.
Glad you liked it! 🙏😊
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!
Thank you Mynda for this insightful Video 📹 👏
You are most welcome! 😊
I am so many thank you, I know more new knowledge.
You are most welcome! 😊
Mind Bending is Correct! Another great video.
Thanks so much!
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 😁
sumproduct = formula array without cntr+shift+enter AVGif = sumproduct/sumproduct
You got it!
Great tip on the use of SIGN()
Glad it was helpful!
Brilliant Mynda
Thanks so much!
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: ua-cam.com/video/ZCQAweoAdOw/v-deo.html
I haven't speed tested FILTER against SUMPRODUCT, so there may be a use case there.
Hi Mynda,
How can we evaluate row and column and/or condutions of array by using Sumproduct?
Not sure what you mean by 'evaluate'?
Oh boy That - - blow my mind 😮
Awesome to hear! 🙏
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 (:
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??
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 😊
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! 🙏😊
Amazing!
Thank you! Cheers! 😊
UAU!!!!! Many thanks for the content! amazing!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thanks so much! 🙏😁
Well explained!
Thank you! 😊
thanks
You're welcome!
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!
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: ua-cam.com/video/L4BuUzccLpo/v-deo.html
If put inside countif for multiple Or, make magic too
Not following.
SIGN function ! That was my big takeaway. I’ll put it to work immediately.
Awesome to hear!
Great video
❤😎
😁🙏👀
Love you teacher Subscribe is done
Wonderful! 🙏😊
@@MyOnlineTrainingHub make a video on back office job
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 😁
Thanks!
Welcome! 😊
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!
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.
Thank you Mynda... And I thought I was an Excel Ninja... :( 😥
Aw, too kind 🙏
Awesome
Thank you!
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 👍
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.
👍😎✊
🙏😁