I have never listened to a more interestingly presented tech tutorial in my life. It was extremely useful, perfectly explained, fun, and even funny actually. Great job, really. Thank you ❤
Thank-you! This is exactly what I was looking for. I needed to write a formula to sum only the negative numbers in a column depending on a filter that was applied, (in this case, production lines). I was only concerned about the products that were behind schedule. This did the trick!
Nice tutorial.. thanks for all the AGGREGATE and SUBTOTAL function tips. Very helpful. Thumbs up! PS - A great keyboard shortcut for grouping / ungrouping is: SHIFT+ALT+RIGHT ARROW (to group) or LEFT ARROW (to ungroup). I use this one daily.
@@TeachExcel Agreed.. I once said "AGGREGATE" to a client and he said "AGRI.. what?".. haha! Was trying to solve Matt C's question below about SUM or AVERAGE hiding columns instead of rows. As far as I can tell, no built in EXCEL function gives you that facility when hiding columns like is available when hiding rows. I tried a UDF.. wrote the code and it worked, but would only update the sum or average if I forced a calc with CTRL+ALT+F9.. even after defining the function as Application.Volatile. I thought.. ok.. worksheet event.. but there is no worksheet event that recognizes hiding or unhiding columns or rows.. grrrrr.. there is a way to trap for a XLM ribbon event procedure to recognize the hide/unhide action.. but.. too much trouble at this point. Better for Matt C to transpose his data or some other manual solution and use AGGREGATE or SUBTOTAL. Curious if you come up with a way to do columns. Thanks for your videos.. always learn something new. Thumbs Up!
@@wayneedmondson1065 It's amazing the worksheet_change event doesn't pick up hiding columns but it doesn't. I wondered if you could get around this using a Worksheet_SelectionChange event, with an Application.Undo command to move back to the previous selection and detect whether it is now hidden....but I can't get it to work, plus don't know how to redo the action after detection.
It's so easy to get stuck in old ways sometimes....I've still been using SUBTOTAL and hadn't noticed AGGREGATE is so much better. Continuous learning is the way to go 👍💚
This is a good video. However, by creating an Excel table with a total row the same results can be obtained. I have an older version of Excel because I don't want to pay Microsoft for every month or year I use the product. What I would really like to know is how to get the sum of a row when some of the columns are hidden. Can you please provide some insight for this?
This is great, but I have a different use. What happens if I have individual cells I am adding individual cells and then hiding columns, but want the total to aonly represent the non-hidden cells in those columns. Example - adding in A2 I am adding B2 + C2 + D2 + E2. I then hide colums D and E, so want cell A2 to reflect just B and C. IS that possible?
This is what I want to do, except my formula isn't a math equation, it's to pull the text from a column to use in another location via copy/paste. I have an attendance sheet. Student names with columns for dates. I can mark students present or absent with a "P" or "A." I then can filter out the present students and am left with only the rows of students who were marked absent that day. What I would like is to use a formula like this: =A2&";"&A3 which would return this string: student1;student2 (student names separated by a semicolon). The only problem is, i don't know how to make the formula only pull data from the visible rows. If I have 30 students, my formula would have to include A2-A31 and so the data is included whether the row is hidden or not. Do you have an idea on how to do this?
Hi Thomas, please ask in our forum and you can upload a sample file or input sample data into the question - it's much much easier to respond there as I will not get a notification of you responding to this comment. Also, mention which version of Excel you have, Excel 365 has some great new functions that would probably be perfect in your case, especially the FILTER() function. FILTER() tutorial: ruclips.net/video/u96imqGMBDM/видео.html Our Excel Forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
I have never listened to a more interestingly presented tech tutorial in my life. It was extremely useful, perfectly explained, fun, and even funny actually. Great job, really. Thank you ❤
You probably won’t see this but thank you so much! You saved me hours of figuring this out for my homework 😂
I'm very glad I could help you out Kaiser!!! :)) Good luck with the homework!
Thank-you! This is exactly what I was looking for. I needed to write a formula to sum only the negative numbers in a column depending on a filter that was applied, (in this case, production lines). I was only concerned about the products that were behind schedule. This did the trick!
I'm very glad it helped :)
Excellent Video... easy to follow and work in excel while you explain...t...thank you so much... Applause Applause Applause!!!
Good, clear explanation. Thank you.
Thank you sir! Glad you think so :)
Nice tutorial.. thanks for all the AGGREGATE and SUBTOTAL function tips. Very helpful. Thumbs up!
PS - A great keyboard shortcut for grouping / ungrouping is: SHIFT+ALT+RIGHT ARROW (to group) or LEFT ARROW (to ungroup). I use this one daily.
You're very welcome, and nice shortcut! Oddly enough, these functions don't seem to be too widely used, in my experience.
@@TeachExcel Agreed.. I once said "AGGREGATE" to a client and he said "AGRI.. what?".. haha! Was trying to solve Matt C's question below about SUM or AVERAGE hiding columns instead of rows. As far as I can tell, no built in EXCEL function gives you that facility when hiding columns like is available when hiding rows. I tried a UDF.. wrote the code and it worked, but would only update the sum or average if I forced a calc with CTRL+ALT+F9.. even after defining the function as Application.Volatile. I thought.. ok.. worksheet event.. but there is no worksheet event that recognizes hiding or unhiding columns or rows.. grrrrr.. there is a way to trap for a XLM ribbon event procedure to recognize the hide/unhide action.. but.. too much trouble at this point. Better for Matt C to transpose his data or some other manual solution and use AGGREGATE or SUBTOTAL. Curious if you come up with a way to do columns. Thanks for your videos.. always learn something new. Thumbs Up!
@@wayneedmondson1065 It's amazing the worksheet_change event doesn't pick up hiding columns but it doesn't. I wondered if you could get around this using a Worksheet_SelectionChange event, with an Application.Undo command to move back to the previous selection and detect whether it is now hidden....but I can't get it to work, plus don't know how to redo the action after detection.
I wonder how did I miss these in my knowledge collection... Very valuable, thanks!
Very Easy to understand, thanks
Nice job. Smashing it lately 👌
Thanks)) I'm trying hard to keep the topics relevant and interesting)
Yes! it worked. thank you.
Superb video it was an eye opener for me.
A great explanation. Thank you!!!
It's so easy to get stuck in old ways sometimes....I've still been using SUBTOTAL and hadn't noticed AGGREGATE is so much better. Continuous learning is the way to go 👍💚
This is a good video. However, by creating an Excel table with a total row the same results can be obtained. I have an older version of Excel because I don't want to pay Microsoft for every month or year I use the product. What I would really like to know is how to get the sum of a row when some of the columns are hidden. Can you please provide some insight for this?
Really Helpful Tutorial Thank You Sir!
You're welcome!)
This is great, but I have a different use. What happens if I have individual cells I am adding individual cells and then hiding columns, but want the total to aonly represent the non-hidden cells in those columns.
Example - adding in A2 I am adding B2 + C2 + D2 + E2. I then hide colums D and E, so want cell A2 to reflect just B and C. IS that possible?
How do you use the match function to apply to visible cells only?
How can I extract cells ignoring blanks. I don't have Filter func
Is there a way to use countif to not count hidden rows
How we calculate sum or average or ... of a range if hide column?
Just make the formula before the column is hidden and hide it - this technique is often used to hide "helper" data in Excel.
This is what I want to do, except my formula isn't a math equation, it's to pull the text from a column to use in another location via copy/paste. I have an attendance sheet. Student names with columns for dates. I can mark students present or absent with a "P" or "A." I then can filter out the present students and am left with only the rows of students who were marked absent that day. What I would like is to use a formula like this: =A2&";"&A3 which would return this string: student1;student2 (student names separated by a semicolon). The only problem is, i don't know how to make the formula only pull data from the visible rows. If I have 30 students, my formula would have to include A2-A31 and so the data is included whether the row is hidden or not. Do you have an idea on how to do this?
Hi Thomas, please ask in our forum and you can upload a sample file or input sample data into the question - it's much much easier to respond there as I will not get a notification of you responding to this comment. Also, mention which version of Excel you have, Excel 365 has some great new functions that would probably be perfect in your case, especially the FILTER() function.
FILTER() tutorial: ruclips.net/video/u96imqGMBDM/видео.html
Our Excel Forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
How to use countifs formula for visible cells
AGGREGATE or SUBTOTAL would be so much easier if they included COUNT BLANK or COUNT IF if that was just as inportant.