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!
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!
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.
Love this Lady. Her explanations are always perfect. Btw, we think the average price should be a weighted average price since the quantities for each price differ, but that can also be done using the sumproduct function. I used this function many times before, but never saw it this elaborate. Once more, thank you.
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!
Your videos are amazing, i have a doubt regarding if lot of datas are there with respect to date...for example for 20 years .how to get month average of every month without using match or lookup or any cell reference formula.
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.
@@LeilaGharani Wow... I'm very courious of it... what kind of task could it have been where I use sumproduct instead of countifs and sumifs? Could you or maybe Kapil P please give an example?
Meanwhile I studied "sumproduct", now I know that it can be used as countif (count quantity, as in this video, no numbers will be counted) and sumif (when there is only 1 array, so there's no multiplicaton)
First, I have only recently discovered your TH-cam 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 SUMPRODUCT_Excel_Explained_Free_Worknook_Xelplus: 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!
Hi Leila, you have made life so easy for all of us. The only thing one has to do is turn on TH-cam and watch your channel. You have so precisely explained use of comma and aster sign while using SUMPRODUCT.
@@LeilaGharani Thanks for your lovely words :). Taking this SUMPRODUCT a step further, request you to upload video on using SUMPRODUCT to add up values containing specific whole number in range of cells :)
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!
You're very welcome. Glad you liked the explanation. True. SUMPRODUCT is so versatile by itself and on top it can be combined with other functions to create impressive one formula arrays.
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.
Who says there is no such thing as free lunch........ knowledge i always have had from u The Leila is tremendous.....I have no words to express my thanks....... Video is wowwwwwwwwwwwwwweee....
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!
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.
@ Leila Gharani, your channel is amazing. Every time I watch a video, I gain specific useful knowledge to put in good use. Thank you for the great videos.
@@LeilaGharani , the content you provide is outstanding. I've had a personal project for several years now. I've been improving it using the knowledge from your videos. Due to the episode explaining INDEX and MATCH, and their combination, I was able to get rid of the clunky VLOOKUPs. I was in the process of implementing new features and automation to the project (struggling for several days, stacking IFs, ANDs, ORs, NOWs and INTs on top of one another) when I stumbled on this video, I could quite easily understand how the SUMPRODUCT function works and how powerful it really is. Then, in a matter of less than an hour, I managed to solve my problems with a concise and neat expanded SUMPRODUCT function, or as it was later called in my notes "The Magic Formula". Once again, your content is really helpful, comprehensive and easy to understand and implement. *THUMBS UP*
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
i stumbled into your videos while bored at work. i love your explanations, great work! now i have to watch all your videos and pick up all the cool tricks!
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!
@@LeilaGharani there are other similar ones, unknown and under-rated. DGET for example is an older function to VLOOKUP, but much more versatile. Maybe you should do a video on such not so popular functions
Yeah, I agree, I even have done a few of her courses online through Udemy. At my work now I am now considered a bit of an Excel Guru... These videos have really helped.
I amazed with your presentation, explanation and giving understanding. I never ever come across like this teaching, like you your math’s skills are superb. Please help me for giving 1st rank, you or your teachings.
Thanks, didn't know why some of my formulas worked with a coma, but not with a *. Now I do, thanks (understanding what Excel is doing with those true/falso and 1/0 made the difference).
SUMPRODUCT is very useful in load distribution problems. In aviation, the center of gravity (CG) of a cargo/passenger load is important for aircraft handling and safety. If one calculates the centers of mass of cargo containers/pallets and people times their individual masses (using SUMPRODUCT) and divides that number by the total weight (using SUM), the result is the CG of the total load. This CG is then used to compare its location within the CG ranges that are allowable for safe takeoffs and landings and economic fuel burn.
The problem watching your videos is that I can not wait till the end of the tutorial to press LIKE, I usually like and then keep watching. You are great Leila. Thanks.
Good information here and clearly explained as always. You need to be very careful using + as an OR operator though... It's perfectly fine in this scenario where only one of the conditions can be TRUE (ie. a company value cannot be both Company A and Company C). In more complex boolean operations where multiple conditions of the OR statement can be TRUE, this method will not work because the term will be evaluated as a count of the number of TRUE conditions. For example, if (for whatever reason) you wanted to return [Company = Company A] OR [Month = Feb]. In this scenario, it would return a revenue of (2 x 60000) + (1 x 10000) + (1 x 18000) + (1 x 38500) + (1 x 22200) + (2 x 36000) + (1 x 16000). This is an incorrect result because it is counting the 60000 and the 36000 twice.
Hi Leila, in this video you have the date split in Months and Years. What if you had a range with dates and then you want to sort data per month where as you can select mmmm-yy?
Sumproduct parameters can be arrays (A1:A10, B1:B10) or list of numbers, e.g. ({1,2,3}, {2,2,1}). But, can I list instead of numbers cell adresses or/and formulas? Eg.: Sumproduct ({Vlookup, C3, Index, {Index, C4, Hlookup})
can you make videos on ROW function and MOD function because we have confusion to use these functions. if you already shared video about this, please share link
Leila Gharani, I found your videos great and helpful. You are doing a great work. Your mode of delivering information and your presentation is best of best. Slowly and steadily you focus on each and every point and make lecture complete. May God reward you for this kind work. Wish you success. Thanks
Wow..... Just wow! This helps a lot with my excel problems. Still watching a lot of your contents and they are all very helpful since you're so good at explaining the concept really well. You have an exceptional/genius skills in excel! I'll watch them all and proceed with your VBA next. 😉
SUMPRODUCT should be used wisely. I was admired of this powerfull formula and started to use SUMPRODUCT everywhere instead of SUMIFS and COUNTIFS. But eventually I felt slow down of calculations :-) So I ran a simple test of both formulas on 10k rows. SUMIFS calculated them in 12,8 seconds, and SUMPRODUCT did the same in 140 seconds.
Leila, thanks for sharing this exquisite function. Usually we tend to use countif/s, sumif/s for they are quite a simpler than sumproduct. The slight rigidity of sumproduct is in understanding use of "*" ,"+" and "=". This will take a person little while to get hang on to. But still surely useful. Thanks again...
Yes, true - we have to be careful with the brackets too when using SUMPRODUCT. But the great thing about it, is it can simply do OR conditions which the SUMIFS can't do easily - (unless we use two or more SUMIFS functions or wrap the SUMIFS inside a SUMPRODUCT function :) )
WOW! It's a much better formula than countif/ sumif. Learned it last year from your video. But went back to old habit without even realizing. Good to rediscover this gold nugget.
First of all I would like to say that your tutorials are amazing. I'm having a hard time overcoming a problem and I really appreciate your support. I have a holiday form that receives the following information in a spreadsheet: email, start date of the holidays, end date of the holiday I need that when a new order is entered, compare it with all previous ones to verify that the same person is not duplicating orders or overlapping days already ordered. I am using the sumproduct function but I am not achieving the goals
Glad you like the tutorials. With regard to your question - not sure without seeing the problem....At the moment though I have a lot of projects that need to get done...best would to post your specific question either here: www.mrexcel.com/forum or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others...
Thanks, well said. I used SUMPRODUCT function in lieu of SUMIFS when it comes to text amounts column without need to convert the column to a number. Is there really no way to include in the SUMIFS function to convert text amount to a number? Thank you for your response.
Can you tell how to write inside a Sumproduct formula to find a partial text (after = how to write) also the same how we can write by referring a cell to find partial text
Hi Leila, very interesting video, I was familiar with SUMPRODUCT but not at the extent of using it to handle exceptions. I have been playing with it a bit and found another way to instruct excel to turn TRUE/FALSE into 1 and 0, and that is by using the N function, e.g. =SUMPRODUCT(N(B6:B11=B18),D6:D11). What do you think?
Actually - Excel has always been able to do all of these things. The keys were knowing that 1). Excel treats ranges as math arrays, 2) that Excel understands Boolean logic and math, and 3). how to use those to do the things Leila described. The things Leila describes in this video are all about Boolean logic. Once you have a Boolean test result inside parentheses these can then be multiplied by something or added, subtracted or divided, the type changes from logical true false to 1 and 0 respectively. This is actually quite easy to understand and use. Now you can leverage that to use it in formulas. This same process is used and forms a basis of ladder logic programming used in industrial process controllers. Ladder logic is a radically different and often vastly more efficient form of computer programming. It strips away all of the language and uses logic to drive programming. For the problems Leila described using sumproduct, almost all work with sum(), sumif(). Etc. Provided the logic is properly structured. Also, you can mix these Boolean expressions and logic used in Excel formulas. Is extremely powerful. You just have to understand a couple key concepts to use them. This can be used to simplify many nested IF statements. If all of the IF statements in a compound IF formula resolve the same way, you can combine them by using logical tests together. Let’s say you have three IF conditions A, B and C. If you want to test X (a cell or range) for the case where any one of these is true, then use IF( (X=“A”) + (X=“B”) + (X=“C”) , “TRUE”, “”FALSE”). Addition results in an answer where the sum is 1 or more if any of the conditions is true, and that resolves as true in the IF formula. If you only want results where ALL three cases are true, substitute multiplication for addition. If any one of the tests is false it resolves to 0 and multiplying by the other two also resolves to 0 hence false. You can create much more complex logical tests than that for different and more complex cases. These are far easier to understand when they are written in Boolean than they are in complex nested IF statements. You can also return results (as Leila did here) which are the result of array multiplication. Be careful though, when Excel tables are involved things can get tricky. You can easily get #SPILL! and other errors when you use or mix array math and tables. You may also get a result that spills when you expected a single result. In many cases the answer is to use the non-array versions in tables, then sum the column.
I am very grateful to you Leila, you really opened my eyes to learn something truly nice and useful and absolutely helpful. You are more than amazing. I like your videos so much.
Thanks for the video, I've had sumproduct on a test for the job today and I never used it before (only used sumif/s) and couldn't figure out it, guess the +, *, = and brackets is what I should have known about, but now I'm ready for the next time :)
as an Excel lover, I really like the way you share video with us. Thanks so much note/ that is my first comment on youtube so far as I watched so many videos here, but you deserve it. cheers Hemn
With your excellent explanation, now i understand how SUMPRODUCT works.. but I have a question on how to total up from multiple worksheet? Example I have 10 worksheets represent by each region and in each worksheet there are items listed in Rows and Years in columns (eg> 2012 till 2020). Now I need to do a summary worksheet to compare any 2 years for selected items in total value of all the region. From your explanation on SUMPRODUCT i can easily get value for individual region only, but what if I need the total the value from the 10 worksheets with Sumproduct condition. I still cannot get it. Please help. Thanks.
Grab the file I used in the video from here 👉 pages.xelplus.com/sumproduct-file
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!
I‘m very glad to hear that Corey! Thank you.
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!
I'm glad I could help with that Kai.
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.
Love this Lady. Her explanations are always perfect. Btw, we think the average price should be a weighted average price since the quantities for each price differ, but that can also be done using the sumproduct function. I used this function many times before, but never saw it this elaborate. Once more, thank you.
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!
It happens to me all the time :)
Your videos are amazing, i have a doubt regarding if lot of datas are there with respect to date...for example for 20 years .how to get month average of every month without using match or lookup or any cell reference formula.
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.
I'm glad it was helpful!
@@LeilaGharani Wow... I'm very courious of it... what kind of task could it have been where I use sumproduct instead of countifs and sumifs? Could you or maybe Kapil P please give an example?
Meanwhile I studied "sumproduct", now I know that it can be used as countif (count quantity, as in this video, no numbers will be counted) and sumif (when there is only 1 array, so there's no multiplicaton)
@@LeilaGharani there any way I can add up a certain product in a list of many different items?
The hot tip for the + meaning or, blew my mind. Didn't know about it. Thank you so much!
You are so welcome, Roberto!
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!
I'm already happy with the one thumbs up :) I'm glad the video is helpful Thomas.
I love Leila!!! My favorite excel teacher!! I recommend her to everyone who wishes to expand their knowledge about any aspect of this tool!!!
First, I have only recently discovered your TH-cam 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 SUMPRODUCT_Excel_Explained_Free_Worknook_Xelplus: 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!
Hi Leila, you have made life so easy for all of us. The only thing one has to do is turn on TH-cam and watch your channel. You have so precisely explained use of comma and aster sign while using SUMPRODUCT.
Thank you so much Sachin for your continuous support. It means a lot!
@@LeilaGharani Thanks for your lovely words :). Taking this SUMPRODUCT a step further, request you to upload video on using SUMPRODUCT to add up values containing specific whole number in range of cells :)
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!
You're very welcome. Glad you liked the explanation. True. SUMPRODUCT is so versatile by itself and on top it can be combined with other functions to create impressive one formula arrays.
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.
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
i’ve worked with sumproduct but this segment cleared up unanswered questions. Thank you!
each time I see your video I know I'm going to be surprised. This one is not an exception. Thank you for sharing.
Who says there is no such thing as free lunch........ knowledge i always have had from u The Leila is tremendous.....I have no words to express my thanks....... Video is wowwwwwwwwwwwwwweee....
I'm glad you like it. Many thanks for the kind feedback.
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!
Glad it's helpful for you. Sumproduct really can be quite useful.
The simplicity and elegance of this use of sumproduct is genious, you rock!! 😀
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.
Trust me, your style of teaching and logic is so simple yet powerful
Thank you!
Many new functions in Excel, but this video is like about a new star too. Very inspiring, thank you!
@
Leila Gharani, your channel is amazing. Every time I watch a video, I gain specific useful knowledge to put in good use. Thank you for the great videos.
You're very welcome. I'm glad the tutorials are helpful.
@@LeilaGharani , the content you provide is outstanding. I've had a personal project for several years now. I've been improving it using the knowledge from your videos. Due to the episode explaining INDEX and MATCH, and their combination, I was able to get rid of the clunky VLOOKUPs. I was in the process of implementing new features and automation to the project (struggling for several days, stacking IFs, ANDs, ORs, NOWs and INTs on top of one another) when I stumbled on this video, I could quite easily understand how the SUMPRODUCT function works and how powerful it really is. Then, in a matter of less than an hour, I managed to solve my problems with a concise and neat expanded SUMPRODUCT function, or as it was later called in my notes "The Magic Formula". Once again, your content is really helpful, comprehensive and easy to understand and implement. *THUMBS UP*
It's so great to hear that you could apply knowledge learned from my videos to your projects. Makes me happy :)
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
Very true. It really is a powerful function that is often overlooked.
i stumbled into your videos while bored at work. i love your explanations, great work! now i have to watch all your videos and pick up all the cool tricks!
Thank you! I'm glad to hear that :)
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!
Thank you! I'm glad to hear that snicho :)
I had been watching your videos for a while, but this somehow slipped me. What you can do with Sumproduct is just another level!!
It really is an underrated function!
@@LeilaGharani Agree, totally!
@@LeilaGharani there are other similar ones, unknown and under-rated. DGET for example is an older function to VLOOKUP, but much more versatile. Maybe you should do a video on such not so popular functions
One of the best teachers on the internet. Bravo.
Yeah, I agree, I even have done a few of her courses online through Udemy.
At my work now I am now considered a bit of an Excel Guru... These videos have really helped.
It is really amaizing that, this video is free !!. Excellent work Leila. Sumproduct is really an amaizing and extreemly usefull function
I amazed with your presentation, explanation and giving understanding. I never ever come across like this teaching, like you your math’s skills are superb. Please help me for giving 1st rank, you or your teachings.
Thanks for the very kind words. Very glad to hear the explanation & content is clear :)
Thanks, didn't know why some of my formulas worked with a coma, but not with a *. Now I do, thanks (understanding what Excel is doing with those true/falso and 1/0 made the difference).
I am a priest and I respect you as my own mother! You are a great teacher & Guide.
I m sending my blessings to you
Nah you're not a priest.
I am.
Excellent. I was little bit tensed. How and where I will use sumproduct. You make me clear. Thanks.
This revealed the secrets of the almighty and powerful sumproduct for me. Seriously, great examples and explanations. Thank you!
Glad to help Morgan. Hope it'll come in handy for you.
This is an amazing video, even though English is a foreign language for me but the way you explained the sum-product function, very easily learned it.
Great to hear!
Thanks!
Thank you!
SUMPRODUCT is very useful in load distribution problems. In aviation, the center of gravity (CG) of a cargo/passenger load is important for aircraft handling and safety. If one calculates the centers of mass of cargo containers/pallets and people times their individual masses (using SUMPRODUCT) and divides that number by the total weight (using SUM), the result is the CG of the total load. This CG is then used to compare its location within the CG ranges that are allowable for safe takeoffs and landings and economic fuel burn.
Thanks for your feedback on a practical application.
Leila Gharani You’re welcome. My submission is only one of myriad non-financial uses for Excel.
You can use OR in sumifs and countifs by providing your criteria as an array {"C1", "C2", "C3"...} instead of one criteria per range
Easy way of teaching complex formulas. Appreciate your hard work❤.
The problem watching your videos is that I can not wait till the end of the tutorial to press LIKE, I usually like and then keep watching. You are great Leila. Thanks.
I have no problem with that :)
Thank you for all your efforts providing all Excel users with valuable Information.
Good information here and clearly explained as always. You need to be very careful using + as an OR operator though... It's perfectly fine in this scenario where only one of the conditions can be TRUE (ie. a company value cannot be both Company A and Company C). In more complex boolean operations where multiple conditions of the OR statement can be TRUE, this method will not work because the term will be evaluated as a count of the number of TRUE conditions.
For example, if (for whatever reason) you wanted to return [Company = Company A] OR [Month = Feb]. In this scenario, it would return a revenue of (2 x 60000) + (1 x 10000) + (1 x 18000) + (1 x 38500) + (1 x 22200) + (2 x 36000) + (1 x 16000). This is an incorrect result because it is counting the 60000 and the 36000 twice.
Hi Leila, in this video you have the date split in Months and Years. What if you had a range with dates and then you want to sort data per month where as you can select mmmm-yy?
Sumproduct parameters can be arrays (A1:A10, B1:B10) or list of numbers, e.g. ({1,2,3}, {2,2,1}).
But, can I list instead of numbers cell adresses or/and formulas?
Eg.: Sumproduct ({Vlookup, C3, Index, {Index, C4, Hlookup})
Wow, with your teaching I have worked out where I was going wrong with sumproduct. Thanks!
Glad to help Irene.
can you make videos on ROW function and MOD function because we have confusion to use these functions. if you already shared video about this, please share link
This is the best excel tutorial channel I have found so far! Great job!
That's a mega compliment! Thank You!!! and also for your support.
Agreed
really awesome tutorial, I can not tell you how many problems this video has solved for me..... 10000000 likes
Super clear demonstration. Short and precise. Thank you!
Leila Gharani, I found your videos great and helpful. You are doing a great work. Your mode of delivering information and your presentation is best of best. Slowly and steadily you focus on each and every point and make lecture complete. May God reward you for this kind work. Wish you success. Thanks
Thank you so much for your comment! Makes me very happy to hear you find the videos helpful.
great function. thank you for this video to know how we can use it. it's easy to learn with you and good example.
your teaching methods is very easy and effective. thank you so much for that
Just found this video and it's very helpful. This is a relatively unknown but elegant and powerful function.
Glad you like it!
Wow!, I am an advanced excel user and never knew that we can do so much using Sumproduct. Thanka Leila :)
I'm glad the video is helpful!
Wow..... Just wow! This helps a lot with my excel problems.
Still watching a lot of your contents and they are all very helpful since you're so good at explaining the concept really well.
You have an exceptional/genius skills in excel! I'll watch them all and proceed with your VBA next. 😉
SUMPRODUCT should be used wisely. I was admired of this powerfull formula and started to use SUMPRODUCT everywhere instead of SUMIFS and COUNTIFS. But eventually I felt slow down of calculations :-) So I ran a simple test of both formulas on 10k rows. SUMIFS calculated them in 12,8 seconds, and SUMPRODUCT did the same in 140 seconds.
Thanks in a million. What a twist. You got the best stuff on earth. SUMProduct is awesome. I have subscribed. A++
Thanks for the sub, John!
Best instructor! Bar none! You make it so comprehensive!
Wow, thank you Darcy!
Leila, thanks for sharing this exquisite function. Usually we tend to use countif/s, sumif/s for they are quite a simpler than sumproduct. The slight rigidity of sumproduct is in understanding use of "*" ,"+" and "=". This will take a person little while to get hang on to. But still surely useful. Thanks again...
Yes, true - we have to be careful with the brackets too when using SUMPRODUCT. But the great thing about it, is it can simply do OR conditions which the SUMIFS can't do easily - (unless we use two or more SUMIFS functions or wrap the SUMIFS inside a SUMPRODUCT function :) )
@Leila is the Goddess of Excel...this video had answers to lot of my queries...thank you..u are the best
LG! This was really good. I never knew this! It makes match-index complications hell of a simple thing! Thank you very much.
Glad to help :)
Textbooks never go in to the why or the practical use of functions. Thank you for making this ❤️
You're so welcome!
How fantastic your interdiction for SUMPRODUCT formula. It is useful for me, Thank you Leila.
I'm happy to hear you find it useful. Thank you for the feedback.
FINALLY, someone who explains this in a way that makes sense! Thank you.
You're very welcome. I'm glad it makes sense :)
the easiest and clearest explanation, many thanks, wish you always the best. please keep it up
You are great at explaining. I learned much of this already but you explain how and why it works superbly!
Thank you! I'm glad you find the tutorials easy to follow :)
Very well done. Thank you. The -- in front of a True/False string transform it in a 1/0 string so we may use for math operations.
WOW! It's a much better formula than countif/ sumif. Learned it last year from your video. But went back to old habit without even realizing. Good to rediscover this gold nugget.
Glad it was helpful!
First of all I would like to say that your tutorials are amazing.
I'm having a hard time overcoming a problem and I really appreciate your support.
I have a holiday form that receives the following information in a spreadsheet:
email, start date of the holidays, end date of the holiday
I need that when a new order is entered, compare it with all previous ones to verify that the same person is not duplicating orders or overlapping days already ordered.
I am using the sumproduct function but I am not achieving the goals
Glad you like the tutorials. With regard to your question - not sure without seeing the problem....At the moment though I have a lot of projects that need to get done...best would to post your specific question either here: www.mrexcel.com/forum or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others...
Thanks, well said. I used SUMPRODUCT function in lieu of SUMIFS when it comes to text amounts column without need to convert the column to a number.
Is there really no way to include in the SUMIFS function to convert text amount to a number? Thank you for your response.
Can you tell how to write inside a Sumproduct formula to find a partial text (after = how to write) also the same how we can write by referring a cell to find partial text
EXCELLENT explanation. You do a marvelous job explaining a complex function. Thank you!
You're welcome Robert. Glad you like the explanation.
Hi Leila, very interesting video, I was familiar with SUMPRODUCT but not at the extent of using it to handle exceptions. I have been playing with it a bit and found another way to instruct excel to turn TRUE/FALSE into 1 and 0, and that is by using the N function, e.g. =SUMPRODUCT(N(B6:B11=B18),D6:D11). What do you think?
Hi liela, first of all I want to say many many thanks for sumproduct formula you solve my big confusion question. This video help full for me 👍👍👍👍
You're very welcome. I'm glad the video is useful!
We can also use Sumif formula to calculate quantity of product A
you are amazing. thank you. such a clear explanation. hot tip of true value as 1 and false value as 0 is brilliant
Actually - Excel has always been able to do all of these things. The keys were knowing that 1). Excel treats ranges as math arrays, 2) that Excel understands Boolean logic and math, and 3). how to use those to do the things Leila described. The things Leila describes in this video are all about Boolean logic. Once you have a Boolean test result inside parentheses these can then be multiplied by something or added, subtracted or divided, the type changes from logical true false to 1 and 0 respectively. This is actually quite easy to understand and use. Now you can leverage that to use it in formulas.
This same process is used and forms a basis of ladder logic programming used in industrial process controllers. Ladder logic is a radically different and often vastly more efficient form of computer programming. It strips away all of the language and uses logic to drive programming. For the problems Leila described using sumproduct, almost all work with sum(), sumif(). Etc. Provided the logic is properly structured. Also, you can mix these Boolean expressions and logic used in Excel formulas. Is extremely powerful. You just have to understand a couple key concepts to use them.
This can be used to simplify many nested IF statements. If all of the IF statements in a compound IF formula resolve the same way, you can combine them by using logical tests together.
Let’s say you have three IF conditions A, B and C. If you want to test X (a cell or range) for the case where any one of these is true, then use IF( (X=“A”) + (X=“B”) + (X=“C”) , “TRUE”, “”FALSE”). Addition results in an answer where the sum is 1 or more if any of the conditions is true, and that resolves as true in the IF formula.
If you only want results where ALL three cases are true, substitute multiplication for addition. If any one of the tests is false it resolves to 0 and multiplying by the other two also resolves to 0 hence false.
You can create much more complex logical tests than that for different and more complex cases. These are far easier to understand when they are written in Boolean than they are in complex nested IF statements.
You can also return results (as Leila did here) which are the result of array multiplication.
Be careful though, when Excel tables are involved things can get tricky. You can easily get #SPILL! and other errors when you use or mix array math and tables. You may also get a result that spills when you expected a single result. In many cases the answer is to use the non-array versions in tables, then sum the column.
Great explanation. It helped me in creating very useful calculations. Thanks.
This is a great video I haven't seen before this kind of video on TH-cam ! You done a great many of people learn lots of thing just because of you ❤️
Glad you enjoyed it!
Hi, I just found gold mine of excel info I was missing. I am really thankful for sharing all these info.
Great to hear!
Your explanation of excel formula's are outstanding.
Your videos are so clear and concise. Thank you so much for all the help you have provided me.
Thank you for the kind feedback, Mark!
Hi Leila, what if instead of text in column A (Company A) you have numbers (product code for instance)? Does sumproduct still works?
Thanks Leila for showing us the + and * trick when doing OR and AND!
I am very grateful to you Leila, you really opened my eyes to learn something truly nice and useful and absolutely helpful. You are more than amazing. I like your videos so much.
I'm really glad it is helpful!
Thank you very much Leila for teaching us Excel functions. Can you kindly do a session on how to use Excel cubes?
Brilliant teaching style, Leila. Your work ethic and the quality of your presentations are inspiring. Thank you for posting these tutorials.
Thank you Bianca! I'm glad you find the tutorials useful.
Thanks for the video, I've had sumproduct on a test for the job today and I never used it before (only used sumif/s) and couldn't figure out it, guess the +, *, = and brackets is what I should have known about, but now I'm ready for the next time :)
Exactly! Always good to be prepared :)
Awesome video. Now I got to know about the real use & value of SumProduct Function
This is wonderful and very helpful, I was able to solve my query after I saw your video, thanks.
Super clear and simple. Wow.....
Great explination of how Excel interprets the information.
Wow just awestruck by your materials and explanations of concepts. Thanks a ton.
You're very welcome. Glad you find the tutorials helpful.
Hi leila.. I am using =sumproduct(--(len(cellnumber or range)=5))....... Please suggest me some alternate way without using sum product..
as an Excel lover, I really like the way you share video with us.
Thanks so much
note/ that is my first comment on youtube so far as I watched so many videos here, but you deserve it.
cheers
Hemn
I'm honored. Especially coming from another Excel lover that means a lot.
Amazing Leila... never knew sumproduct is so useful !! Thanks
Happy to help!
Great......Leila..,really helpful and extraordinary explanations......very very thanks.
With your excellent explanation, now i understand how SUMPRODUCT works.. but I have a question on how to total up from multiple worksheet? Example I have 10 worksheets represent by each region and in each worksheet there are items listed in Rows and Years in columns (eg> 2012 till 2020). Now I need to do a summary worksheet to compare any 2 years for selected items in total value of all the region. From your explanation on SUMPRODUCT i can easily get value for individual region only, but what if I need the total the value from the 10 worksheets with Sumproduct condition. I still cannot get it. Please help. Thanks.
Madam, please explain date wise debit and credit total in two columns and balance in next line.
That's how Excel (and everything) should be taught. Thank you