I was trying to figure this out for hours. Formulas i was finding on youtube did what i wanted but always had one thing missing which was identifying per criteria. Thank you so much for this! Worked 100% for me :)
Thank Mike & Bill. I remember seeing the OFFSET method in your earlier videos and I was suprised at how Offset can be used like this. I like the refrigerator light example :)
Thanks guys for the solution based upon a single criteria. I would like to know if this could be used for multiple criteria? (Kinda like a simple AND function)
This is really helpful, finally i found the solution from my problem... Thanks all ur hardwork.. i tryed to convert this formula into vba funtion but i am unable to catch up. kindly help me..
No, you don't want to be like us! You want to be like you!!!!! Each of us is an awesome and unique Teammate on our EXCElent Online Excel Team!!!!! We love you just as you are!!!
ExcelIsFun hi sir I've tried this with subtotal and aggregate formulas and it does work without offset and without count just change the data to Table and it will work absolutely
So, I tried Mike's function, but it kept returning #N/A. I'm wondering if it's because the number of entries I'm working with is too large. Excel says I can't have more than 8192 characters in the formula, does the OFFSET return count as x number of characters for each row? ie, if my sheet is 6000 rows long, is OFFSET trying to put 6000 numbers + 6000 commas + etc and just crapping out on me?
What a great teaching! thanks a lot but one minor question, could you please let me know using OFFSET Function finding the last row of that column? The range of your example is quite short but what I am going to apply this tech is way too long data, rows about 2000.
thanks for this video, I am facing a problem & I would like if you can help, I have checkboxes using for multi rows and columns, the problem is I am using filler to hide a lot of rows or columns but these all are linked with the checkboxes even when i hide or filter the the data, could you please or anyone to help ?..thanks a lot
Ok so.. it would be easier to use aggregate if helper column is created. However, I have 24 month columns.. so I copied old traditional format.. it worked even though i still don't know what the heck it means haha
What an awesome collaboration, always glad to have you both in a video. 👍
I was trying to figure this out for hours. Formulas i was finding on youtube did what i wanted but always had one thing missing which was identifying per criteria. Thank you so much for this! Worked 100% for me :)
Thank you Mike and Bill for the awesome video.
Thank Mike & Bill. I remember seeing the OFFSET method in your earlier videos and I was suprised at how Offset can be used like this. I like the refrigerator light example :)
I LOVE the refrigerator light example. If you notice, the SUBTOTAL and OFFSET formula element lets you see inside the refrigerator door!!!!!! : )
=SUMIFS(B3:B24,A3:A24,D1,C3:C24,D2)
D1=YES and D2=A or B or C
is that workable instead this?
Hey guys, Its our Leila Madam here..
Super.......Now I feel Excel is fun.
Glad to help you, Raghu V!!!!
Absolutely amazing solutions. Thank you Mr Excel and Mike for this great video 📹 👍
Yes!!!!
Thanks for sharing another amazing video... Excelisfun u rocked again.... GBU
Thank you guys! It helped me a lot! Just remembering those who use the Excel with the metrical system, make sure to replace "," with ";"
Finally!!!! I could reconcile with this approach. Thaaank you so muuch
Mrexcel 2nd try was so simple and easy to learn.... GBU Mr excel . you both made rocked.....
Thanks guys for the solution based upon a single criteria. I would like to know if this could be used for multiple criteria? (Kinda like a simple AND function)
That was fantastic! love the fast type formula without needing a use of an helper columns or an array formula.
That's an awesome formula - and it's helped me enormously. Thank you!
God bless you
Great example of using the OFFSET function in a novel way, though the helper column is probably something I'd use :-)
Yes, indeed, I am often cursed by complexity... : )
So cool! I was able to use subtotal with if function w/ this too
This is really helpful, finally i found the solution from my problem... Thanks all ur hardwork.. i tryed to convert this formula into vba funtion but i am unable to catch up. kindly help me..
The Sumproduct/Subtotal/Offset formula is amazing but if I'm tired I usually go with the helper column approach.
But... That implies when you are not tired, you go with NOT Helper Column : ) Go Team!
This is amazing. Thanks Mike n Bill
You are welcome!!!!
Thanks
OFFSET I need to practice on that :) Thanks Mike and Bill
You are welcome! Thanks for your consistent support : )
you guys ROCK!! :) Thanks
Oh My Goodness, this is awesome!
WOW! Thank you very much!!! You really helped me a lot :)
i like this so much i wish i can be like Mr. Mike and Mr. Bill one day love u guys
No, you don't want to be like us! You want to be like you!!!!! Each of us is an awesome and unique Teammate on our EXCElent Online Excel Team!!!!! We love you just as you are!!!
ExcelIsFun thank u so much sir for your advice and i got what u mean I'm glad u told me that
ExcelIsFun hi sir I've tried this with subtotal and aggregate formulas and it does work without offset and without count just change the data to Table and it will work absolutely
But only for the visible rows...not visible IF Yes.No =Yes (and is still visible).
You can add a helper column to make this work in a Table.
Oakley Turkey yes you are right
Thanks guys. How do we substract contents of 1 column with an other column excluding hidden rows ?
Magic!
How can we element the cells values which contains the Zero in it?
can you do averageifs with this same method? (changeing 109 to 101)?
So, I tried Mike's function, but it kept returning #N/A. I'm wondering if it's because the number of entries I'm working with is too large. Excel says I can't have more than 8192 characters in the formula, does the OFFSET return count as x number of characters for each row? ie, if my sheet is 6000 rows long, is OFFSET trying to put 6000 numbers + 6000 commas + etc and just crapping out on me?
Smart Solution Mike Go Team
Lots of Fun, for sure : ) Go Team!!!!!
how to apply the same formula in google sheets. I did try to apply but its not working?
What a great teaching! thanks a lot but one minor question, could you please let me know using OFFSET Function finding the last row of that column? The range of your example is quite short but what I am going to apply this tech is way too long data, rows about 2000.
It's working fine on my spreadsheet with over 500 rows and about 30 columns
thanks for this video, I am facing a problem & I would like if you can help, I have checkboxes using for multi rows and columns, the problem is I am using filler to hide a lot of rows or columns but these all are linked with the checkboxes even when i hide or filter the the data, could you please or anyone to help ?..thanks a lot
you could have used Subtotal and used filter fist on "B" and then on "Yes" simple
thank you
You are welcome!
Ok so.. it would be easier to use aggregate if helper column is created. However, I have 24 month columns.. so I copied old traditional format.. it worked even though i still don't know what the heck it means haha
You forgot to add the opening theme!
Excel makes this way to complicated for what should be a pretty simple function
How do we get the drop down of the function numbers? Its not displaying as such in my sys. How can I enable it? Thx.
You must have Excel 2010 or later.
I am using 2016. Unable to figure out how to enable such 'function' list selections. Google is not very helpful. How can I enable it?
If you type
=Aggregate(
into a cell, as soon as you type in "(" it will show you the drop down list. There's nothing to enable.
Unfortunately, I am getting any such function selection lists. I am getting the function tips. I am using the Excel 2016 on Mac.
I have NO idea. Are you using online version or a Mac!?!
My Head hurts