It's astounding how you build all this logic when constructing a formula Mike. I've been trying to break this formula down and it just takes ages for me to understand it in a digestible manner.
excellent explanation of both methods. please note that sumproduct should only be used where you are only doing it for a number of cells. For large-scale use, the sumif index match should be used as sumproduct will make recalc times VERY VERY SLOW if it's being used hundreds of times or greater across a large dataset.
What can we do if there's multiple california in the first row? I was using sumproduct, but my file is huge, sumproduct make it run super slow. is there a fast way to find the sum without using sumproduct?
Thanks for the explanation. I have 2 doubts here. Can we use SumifS function instead of this and second what if I need to use only index match function without using concatenate function where there are two criteria one which is horizontally placed and other which is vertically place,pls help
What if we have a very large table that we can't reference each cell individually. e.g. I have a sales report table containing country, gender (m/f) and the revenue generated by each gender from different countries. I need to sum up the revenue from males from a particular country, how do I go about this? I used SUMIFS without Index match and had to change the condition for each country, but I think there should be a way to use sumifs together with index match so that I can copy the formular along.
Thank you so much for such a wonderful and useful video! Would you please let me know if you have a video that shows how to do the same in Excel Power Query? Thank you so much! Eleonora:-)
Hi, is it possible for you to provide assistance with a formula that is not returning the results? I have produced a table which is effectively the same as you have in Excel Magic Trick 714, but the formula is always delivering "0". I think I am very close.
Thank you very much for this content. Very simple to understand and implement. It was very helpful for me to follow your step by step procedure. Keep posting ! Thanks !
Can you do a tutorial in this scenario. All in One row in different column: Subname#1 / Subcost1 ; Subname#2 / Subcost2 ; Subname#3 / Subcost3 ; Subname#4 / Subcost4
Thank you for you videos, Please can you point me to the correct video for this problem. I have a sheet with both Inventory and Purchases on it, i want to calculate stock when entering a new purchase. That is when i enter a new item and i purchased 10 more it updates the Inventory. My inventory list (Item Code) is in Column A( 3 to 24) and the Quantity is in B (3 to 24) I have created a drop down menu in the Purchase column for the item code. I now need to enter a value in the purchase quantity column G3 (3 to 24) and this should add that value to the Quantity in column B.
Thank you very much, I have been looking for this solution for so long and this is exactly what i need!! Have been watching your seminars and they are ultra-helpful!! Thanks a lot again
Thanks for this video, it is very helpful. I am trying to use the match function to provide values that are greater than or equal to numeric values. For example, if you replaced the States with weeks numbers (1,2,3,4,), I need to return all dollar values that are greater than or equal to week two as well as those that are less than week two. I know that you can use the 1 or -1 as the last argument for the match function but my research online makes me believe that the array must be sorted in ascending order for less than and descending for greater than. Any ideas of how to solve for this? Thanks again
Hi Mr ExcelisFun. Thanks for this video. However, I am looking for a formula that helps me solve the below puzzle. I have a data which has three columns on worksheet "sheet1" with titles: "Month (ie January to December occurring multiple times), Account Code (NB: Account code could be for example 1001 to 1009 also occurring multiple) and Amounts varying for each lines. And on "worksheet2", I have one column and one row. The column is "Account Code and the row is "Months" (ie January to December). My question is that am trying to find a combination of formula that will calculate the sum of "account codes" for individual codes (1001 t0 1009) in worksheet1 and sum them by months in worksheet2. That is, for instance, look up all code 1001 for January in worksheet1 and take all their total sums and put it below row January in worksheet2, then do the same for code 1002 and so on. I wouldn't mind sending you a template if I can get your email address. Thank you so much.
I have 7 years of date for different companies (data range is different for each company e.g. for company A I have data from 2007-2013 and for B I have from 2003-2009), the data value changes in a certain year for each company (for example for first company the change happens in 2010 and for the second it happens in 2006). I have the date of change and already concatenate it with the company's unique code. I want to sum 3 years before change and 3 years after changes. Please help me with that. I appreciate.
october sancho 10 october sancho 20 october sancho 30 october sancho 40 october neymar 50 how could i calculate the top 3 scores for sancho in october? i have a data set where 1000 players in october and i need to sum the top three for each player for that month. i tried to do this =IF(AND(E17=C17:C21,F16=B17:B21),SUM(LARGE(D17:D21,{1,2,3}))"") but due to the last part of the if function since it says neymar at the bottom it leaves it blank
It's astounding how you build all this logic when constructing a formula Mike. I've been trying to break this formula down and it just takes ages for me to understand it in a digestible manner.
It takes me just as long to understand. I just work hard and get it under my belt before filming : )
@@excelisfun Got it, I love you videos! They're great!
@@qasimawan3568 : ) : ) All we got to become great is hard work. Which is what makes it fun!
Sumproduct is brilliant ) old school though but still like to use it. Thanks Mike for your hard work throughout all these years :)
You are welcome, Nader!!!! Are you using a old version of Excel so you need these, or are you just having fun with history?
@@excelisfun I have 365 insider edition but I like to have fun with old school methods. It makes me feel that I am better at excel 🤓🤓🤓
@@nadermounir8228 You are better because of it. Knowing history is power : ) : )
You are my savior, this saved a long long day, thank you. You dont understand how much stress you took off
Glad to take some stress away : )
Mike, you did it again. I had no idea how to sum up index and match and you explained it so easily. Outstanding!!!
Always glad to help, Dutch!!!!
Very nice brief concerning these excel functions
excellent explanation of both methods. please note that sumproduct should only be used where you are only doing it for a number of cells. For large-scale use, the sumif index match should be used as sumproduct will make recalc times VERY VERY SLOW if it's being used hundreds of times or greater across a large dataset.
How would you change the formula if there were 2 california columns?
Cool!
What is your formula?
In this case, =SUMIFS wouldn't be easier? You don't even need to create a JOIN column nor =SUMPRODUCT.
@huzcer , Yes SUMIF is much faster calculating than SUMPRODUCT!
What can we do if there's multiple california in the first row? I was using sumproduct, but my file is huge, sumproduct make it run super slow. is there a fast way to find the sum without using sumproduct?
I wish I could like this a thousand times. Thanks for your videos!
Thank you very much for the 10000 likes : )
Thanks for the explanation. I have 2 doubts here. Can we use SumifS function instead of this and second what if I need to use only index match function without using concatenate function where there are two criteria one which is horizontally placed and other which is vertically place,pls help
I second that
What if we have a very large table that we can't reference each cell individually.
e.g. I have a sales report table containing country, gender (m/f) and the revenue generated by each gender from different countries. I need to sum up the revenue from males from a particular country, how do I go about this? I used SUMIFS without Index match and had to change the condition for each country, but I think there should be a way to use sumifs together with index match so that I can copy the formular along.
Hi, I have 2 headers as criteria, same as shown but month wise, pl suggest, how to incorporate 2 way headers in match function
AWESOME! With the help of your video I managed to find a 3-minute-solution to a problem, that I already already spent hours on before!
Thank you so much for such a wonderful and useful video! Would you please let me know if you have a video that shows how to do the same in Excel Power Query? Thank you so much! Eleonora:-)
Great Knowlege, Thanks
You are welcome!
Awesome; saved again by your archives. The sum-product part 2 is really clever.
Glad you like it!
Hi, is it possible for you to provide assistance with a formula that is not returning the results? I have produced a table which is effectively the same as you have in Excel Magic Trick 714, but the formula is always delivering "0". I think I am very close.
Thank you very much for this content. Very simple to understand and implement. It was very helpful for me to follow your step by step procedure. Keep posting ! Thanks !
You are welcome!
NICE VIDEOS
Can you do a tutorial in this scenario. All in One row in different column: Subname#1 / Subcost1 ; Subname#2 / Subcost2 ; Subname#3 / Subcost3 ; Subname#4 / Subcost4
Hello, i wanted to ask you, if you want to sum (QUADSR1 and QUADSR2) for california ??
Thank you for you videos, Please can you point me to the correct video for this problem. I have a sheet with both Inventory and Purchases on it, i want to calculate stock when entering a new purchase. That is when i enter a new item and i purchased 10 more it updates the Inventory.
My inventory list (Item Code) is in Column A( 3 to 24) and the Quantity is in B (3 to 24)
I have created a drop down menu in the Purchase column for the item code.
I now need to enter a value in the purchase quantity column G3 (3 to 24) and this should add that value to the Quantity in column B.
Thank you very much, I have been looking for this solution for so long and this is exactly what i need!! Have been watching your seminars and they are ultra-helpful!! Thanks a lot again
Glad the video helps!
example for "countifs with variable table column and rows simultaneously" ???
Thanks for this video, it is very helpful. I am trying to use the match function to provide values that are greater than or equal to numeric values. For example, if you replaced the States with weeks numbers (1,2,3,4,), I need to return all dollar values that are greater than or equal to week two as well as those that are less than week two. I know that you can use the 1 or -1 as the last argument for the match function but my research online makes me believe that the array must be sorted in ascending order for less than and descending for greater than. Any ideas of how to solve for this? Thanks again
Hi Mr ExcelisFun. Thanks for this video. However, I am looking for a formula that helps me solve the below puzzle.
I have a data which has three columns on worksheet "sheet1" with titles: "Month (ie January to December occurring multiple times), Account Code (NB: Account code could be for example 1001 to 1009 also occurring multiple) and Amounts varying for each lines. And on "worksheet2", I have one column and one row. The column is "Account Code and the row is "Months" (ie January to December).
My question is that am trying to find a combination of formula that will calculate the sum of "account codes" for individual codes (1001 t0 1009) in worksheet1 and sum them by months in worksheet2. That is, for instance, look up all code 1001 for January in worksheet1 and take all their total sums and put it below row January in worksheet2, then do the same for code 1002 and so on. I wouldn't mind sending you a template if I can get your email address. Thank you so much.
I have 7 years of date for different companies (data range is different for each company e.g. for company A I have data from 2007-2013 and for B I have from 2003-2009), the data value changes in a certain year for each company (for example for first company the change happens in 2010 and for the second it happens in 2006). I have the date of change and already concatenate it with the company's unique code. I want to sum 3 years before change and 3 years after changes. Please help me with that.
I appreciate.
This is Fantastic! Really Helped me save a ton of time. Thank you
why not join with 2 ampersands and then vlookup, simple?????????????
october sancho 10
october sancho 20
october sancho 30
october sancho 40
october neymar 50
how could i calculate the top 3 scores for sancho in october? i have a data set where 1000 players in october and i need to sum the top three for each player for that month. i tried to do this =IF(AND(E17=C17:C21,F16=B17:B21),SUM(LARGE(D17:D21,{1,2,3}))"") but due to the last part of the if function since it says neymar at the bottom it leaves it blank
in this same function how to add two date between criteria
You can add between two dates with a SUMIFS like:
=SUMIFS(NumberRange,DateRange,">="&CellWithLowEnd,DateRange,"
Thanks for the video! Great help!
Glad it helped!
Brilliant, makes it sound so simple!
Glad the video helps!
Thank you sir. I would you say you're the best. You've covered almost all on adv Excel. Loads of love:-)
Pudukku...sounds Great
Your help is greatly appreciated!
How did you gain the knowledge of manipulating excel?
Thank you so much for your video... It was very helpful for complicated situations.
You are welcome, Shankar!!!!
Fantastic! Just the formula that I needed. Thank you so much! :)
Nice Work... Thanks
You are really an ace.. hates off
thank you so much for this video! you have gotten me out of a pickle!!!
You are welcome, Jenny Lee!
Even if you think you're an Excel pro, when you come on this channel you start to second guess yourself
No of us are really Excel pros, we are all just having fun with Excel and learning new things all the time!!!
@@excelisfun Great perspective
I love it. solved my problem. thanks so much.
Amazing, Thank you.
Thanks for this!! very helpful.
Iife saver tricks. thanks a bunch.
You are welcome a bunch!!
Very helpful!
very nice
useful thanks
Thanks a lot. This really helped me today.
Thanks a lot!
Perfect!!!