One of your best videos in my opinion. Truly brilliant technique. Array formulas are my weakness in excel, so this is very helpful. Well done Mike! Thank you for all your hard work.
I kept searching for a solution and found one of your videos that offered a couple solutions, either using offset or using sum(index:index) which worked for what I needed, again thanks for your thousands of videos!
You aer welcome, Tail! Thanks for your support with your comment, thumbs up and Sub. Also, you got good search skills to get to just the video you want : )
Try watching the Slaying Excel Dragon's playlist of videos. If you watch and learn all that is in the 53 video series, you will be very good with Excel. There is also an Excel Basics playlist of videos that has 23 videos and will probably get you to the level that your boss requires. Go to the excelisfun channel and watch the channel video that shows how to get the most out of the channel.
Hi, I'm stuck with this problem and found your video really REALLY cool ! I have a question, how do I replace this {0;1;2;3} in the OFFSET in order to have hundred lines instead of 4 as shown in your example ? Thanks again !
Thanks for the video. This doesn't work in google sheets though. I downloaded the speardsheet you posted. When saving as G-sheets, I receive an error that SUMPRODUCT range doesn't match.
I am a beginner in excel, my boss told me I need to be at intermediate level . I know you have thousands of videos on your channel, what playlists, or what videos would you recommend me to watch first? For my job, I use a lot of functions, calculation, such as look up functions, shortcuts. If you can give me some suggestions that will be awesome! Thanks
Hi Mike, What if there is a total is in the column H, not in row 7 or 8, then you hide 2 columns, still excel is counting/adding the hidden column. I try simple subtotal(102,[range]) but it is counting the hidden column. Thank you Sir.
Sorry i am Downloading your Video to watch when i am offline , thank you very much for Help me to improve my Excel ,do you have any think to work ( Word & Number ) together ( Product Mix my main Focus )
Thank you for creating all of your WONDERFUL tutorials. I'd like to create a MEMBERSHIP MANAGEMENT spreadsheet in EXCEL but don't really know how to go around it using shortcuts etc. Can you guide me please? Thank you & Cheers.
Awesome video! I was thinking that the Subtotal function would add all 4 values together prior to returning the individual values to the SumProduct function. Is the reason that it didn't do this because an array of items was returned to the Subtotal function from Offset, rather than a range of items?
sorry to be offtopic but does anyone know of a method to get back into an instagram account?? I stupidly lost the password. I would appreciate any help you can give me.
@Atlas Asher Thanks for your reply. I got to the site through google and im in the hacking process atm. Seems to take a while so I will get back to you later when my account password hopefully is recovered.
I've made a career from skills learned by your videos! I have a tough riddle for you - I have large arrays of data that have data in columns by week, the rows by project number - how do I sum data for a specific project between two dates? Sounds easy, but I can't figure out how to tell the SUMIFS formula to look for a row based on project ID - any suggestions?
I have many videos for adding between an upper and lower date. Here is one for you: ua-cam.com/video/igeUOWs7R9o/v-deo.html Below this video in the show more area, there is a timed hyperlink table of contents so you can jump to any section of the video that you want.
The subtotal function 109 when done in a row instead of a column does not work? if you hide a column, the total value for the row will still be the same. Do you know why?
Another great video as usual there is one dislike on it, I really want to know what actually is inside his/her mind that convinced him/her to click on dislike.
Try watching the Slaying Excel Dragon's playlist of videos. If you watch and learn all that is in the 53 video series, you will be very good with Excel. There is also an Excel Basics playlist of videos that has 23 videos and will probably get you to the level that your boss requires. Go to the excelisfun channel and watch the channel video that shows how to get the most out of the channel.
These two guys have changed the lives of many people, I hope they continue doing it for a long long time!
Yes, that is him: The Excel Master!!!!! He is the guy that started the whole Online Excel Team so many years ago! Woo Hoo!
One of your best videos in my opinion.
Truly brilliant technique.
Array formulas are my weakness in excel, so this is very helpful.
Well done Mike! Thank you for all your hard work.
You never cease to amaze me with the tricks you pull off with Excel! Always my go to source!
I am glad that you liked it!
Watching you & Mr Excel has improved my excel knowledge so much, it has given me more job Opportunities at work. thank you
We will! You too, Gexcel, with all of your videos!
That's an amazing technique ! Iv never seen this use of an array vector inside the OFFSET function !!!! Can't believe it !
Great, ExcelStrategy!! I am glad that is new and helpful!
I am glad that you like it!
I am glad that the videos help!
I kept searching for a solution and found one of your videos that offered a couple solutions, either using offset or using sum(index:index) which worked for what I needed, again thanks for your thousands of videos!
You aer welcome, Tail! Thanks for your support with your comment, thumbs up and Sub. Also, you got good search skills to get to just the video you want : )
❤️ You made my day ! Thankyou
My pleasure 😊
Glad you like it!
Thanks, your videos are singular.
Great explanation, thanks 🙏🏻
You are welcome!
Thanks for all the videos. These will def. help me in the long run.
Awesome!!!! never seem to get enough of these tricks and formulas :) :)
Glad you can't get enough, John : )
Thank you so much, Mike. This formula made my day!
That's so cool. Thanks. It helps me a lot
That will be a great event!
Excellent job Mike. Hey did you see Bill doing array formulas now? Lol. I was very surprised.
Thank you. I watched another video about that couldn't get it to work correctly. I got it on the first try with your video. Thanks!
: ) : ) : )
Thanks, krn14242!
I watch all of Mr Excel's videos! Yes I did see it. I would have done that formula much differently :). Too bad it was not a Duel!
Try watching the Slaying Excel Dragon's playlist of videos. If you watch and learn all that is in the 53 video series, you will be very good with Excel. There is also an Excel Basics playlist of videos that has 23 videos and will probably get you to the level that your boss requires. Go to the excelisfun channel and watch the channel video that shows how to get the most out of the channel.
As usual helpful , thanks a lot
You are welcome a lot, hany!!!
I do not have all the video files available for download. Sorry.
Wow. Nice. I'll have to go back and try to digest this concept of "offset."
Great video, very useful!
Glad it is useful for you, smoreiram!!! Thanks for your support of the excelifun channel at UA-cam : )
Awesome Video which typically for you.
Hi, I'm stuck with this problem and found your video really REALLY cool ! I have a question, how do I replace this {0;1;2;3} in the OFFSET in order to have hundred lines instead of 4 as shown in your example ? Thanks again !
David Davoust oh comeon I have same problem
Mannnn.... I didn't know this earlier..... Awesome mikeee
Yes, this is a really epic trick : ) : ) Thanks for stopping by in the comments, RRR!!!!
@@excelisfun this still surprises me... How does it work.... Do u know any other alternative formula that does this job mike??
@@rrrprogram8667 I do not know of an alternative. The SUBTOTAL and OFFSET method is the only combo I have seen to accomplish this.
@@excelisfun Roger that... Will keep a check on this
GREAT video and explanation! It made my workday 100% better 👍
Glad you liked it, Stacy!!!
As I said in the video, SUBTOTAL cannot handle hidden columns. I do not know of a formula way to deal with hidden columns.
Thanks Sir
You are welcome!
Thank you for this, it was exactly what I was looking for!
Glad it helps : )
Fantastic! Thank you so much.
Thanks for the video. This doesn't work in google sheets though. I downloaded the speardsheet you posted. When saving as G-sheets, I receive an error that SUMPRODUCT range doesn't match.
Same! Anyone come up with a solve for this?
I am a beginner in excel, my boss told me I need to be at intermediate level . I know you have thousands of videos on your channel, what playlists, or what videos would you recommend me to watch first? For my job, I use a lot of functions, calculation, such as look up functions, shortcuts. If you can give me some suggestions that will be awesome! Thanks
Hi Mike,
What if there is a total is in the column H, not in row 7 or 8, then you hide 2 columns, still excel is counting/adding the hidden column.
I try simple subtotal(102,[range]) but it is counting the hidden column.
Thank you Sir.
it is good. i want to work out weighted average in the same way. can you release video ?
Hi u r amazing. How can I download all your videos at once so I can watch them off line?
Excellent - thank you! This explained everything I needed to do.
Great! Glad you liked it!
Do you know of any, such as yourself, high-caliber UA-cam channels for Access, SQL, and or Hadoop?
Thank you so much, really magic.
You are magically welcome!
Sorry i am Downloading your Video to watch when i am offline ,
thank you very much for Help me to improve my Excel ,do you have any think to work ( Word & Number ) together ( Product Mix my main Focus )
I am trying to write the final formula in VBA but hitting roadblocks. Any chance you would have the VBA formula?
Thank you for creating all of your WONDERFUL tutorials. I'd like to create a MEMBERSHIP MANAGEMENT spreadsheet in EXCEL but don't really know how to go around it using shortcuts etc. Can you guide me please? Thank you & Cheers.
For back and forth dialog to get Excl solutions try THE best Excel question site:
mrexcel.com/forum
you saved my 5 hours
Glad it helps!!! Thanks for your support : )
Truly Amazing time saver
Hi i would like to sum pro1 and hide column b
How to do that?
Awesome video! I was thinking that the Subtotal function would add all 4 values together prior to returning the individual values to the SumProduct function. Is the reason that it didn't do this because an array of items was returned to the Subtotal function from Offset, rather than a range of items?
sorry to be offtopic but does anyone know of a method to get back into an instagram account??
I stupidly lost the password. I would appreciate any help you can give me.
@Alejandro Alexis instablaster ;)
@Atlas Asher Thanks for your reply. I got to the site through google and im in the hacking process atm.
Seems to take a while so I will get back to you later when my account password hopefully is recovered.
@Atlas Asher It worked and I finally got access to my account again. I am so happy:D
Thanks so much, you saved my ass !
@Alejandro Alexis happy to help :D
Sir very good video. But can u please give examples on counting TEXT column instead of SUM using the above method. Thank you.
Sir,
But its working only 403 rows,
Is there any option for large data 20k 30k rows or above
Thank you very much sir. ;)
I've made a career from skills learned by your videos! I have a tough riddle for you - I have large arrays of data that have data in columns by week, the rows by project number - how do I sum data for a specific project between two dates? Sounds easy, but I can't figure out how to tell the SUMIFS formula to look for a row based on project ID - any suggestions?
I have many videos for adding between an upper and lower date. Here is one for you: ua-cam.com/video/igeUOWs7R9o/v-deo.html
Below this video in the show more area, there is a timed hyperlink table of contents so you can jump to any section of the video that you want.
I do not understand your question.
Hi,if i have a question ...may I write an e-mail to inquire pls?
What if in the brackets { } I have to input from 0 to 700, I have 700 rows, how do I do that?
I hope same day to do a duel with you Mike and Bill Jelen, in the day when you come to Portugal!!!!
The subtotal function 109 when done in a row instead of a column does not work? if you hide a column, the total value for the row will still be the same. Do you know why?
Use 9 at the place 109
Arrays formulas can replace VBA in many situations
That is true!
Hi, I'm Brazilian, how can I buy your book?
Thank you.
Excellent!
Super Thanks
Anyone can if the study...
Thank you so much,,,,,,
I bet it can be done with a Custom VBA function. Try posting question to:
mrexcel [dot] com/forum
awesome!
i must say you are wonderfull men
Amazing.
A great Access channel is:
youtube [dot] com/user/LearnAccessByCrystal
Another great video as usual
there is one dislike on it, I really want to know what actually is inside his/her mind that convinced him/her to click on dislike.
Thank you for say that. I don't get it either. The video delivers free knowledge. Thanks for your support, Mohammad Taha!!
You're welcome Sir :)
Thanks. A bit confusing, though
Yes, this is a crazy hard one...
I am a beginner user for excel. My boss told me I need to be at immediate level
Do you think that many people can learn VBA? Be sincerely.
I bought the book: VBA and Macros: Excel 2010 (Bill Jelen)
OK. I wish I had seen them before
6:15
09:20
I do not know. Ask the publisher here:
e-mail: pub at mrexcel.com
Bill himself?
Any people?
too fast , i am not able to keep pace with think & view together
I am glad that you liked it!
You are welcome!
Try watching the Slaying Excel Dragon's playlist of videos. If you watch and learn all that is in the 53 video series, you will be very good with Excel. There is also an Excel Basics playlist of videos that has 23 videos and will probably get you to the level that your boss requires. Go to the excelisfun channel and watch the channel video that shows how to get the most out of the channel.
Excellent !
: ) : )
thank you so much
I do not know. Ask the publisher here:
e-mail: pub at mrexcel.com