Append “ALL” to Top of Sorted Unique List for Data Validation List for SUMIFS Function. EMT 1740
HTML-код
- Опубликовано: 6 июл 2024
- Download Excel File: excelisfun.net/files/EMT1740....
Learn how to use “ALL” as a condition in SUMIFS function to add whole column of numbers, but when a specific product is selected, the SUMIFS adds for just that product. Learn the 4 different types of situations. Then learn how to use Microsoft 365 functions, including the LET function to append the condition “ALL” to the top of a sorted unique list of products.
1. (00:00) Introduction
2. (00:29) Intro song
3. (00:37) Manual method to create unique list with ALL as a condition added.
4. (01:17) Data Validation list using manual method.
5. (01:39) SUMIFS Formula #1. Create formula to add whole column when “ALL” is selected. Use IF to use one of two functions: SUM or SUMIFS.
6. (02:43) SUMIFS Formula #2. Create formula to add only numbers where a product name has 1 or more characters using “?*” criteria. See IF function inside the criteria1 argument of SUMIFS. “ALL” is selected.
7. (03:53) SUMIFS Formula #3. Add when product has zero or more characters using “*”.
8. (04:14) SUMIFS Formula #4. Add with Not Empty Criteria using Not Critreria.
9. (04:28) Append “ALL” to top of sorted unique list. See the functions: LET, INDEX, SORT, UNIQUE, SEQUENCE, ROWS and IF.
10. (08:03) Ctrl + C, C keyboard.
11. (08:30) LET Function final formula.
12. (09:59) Data Validation list using LET function output and the Spilled Range Operator.
13. (10:11) Add New Data.
14. (10:31) Summary, Closing and Video Links
=LET"Excel Trickery" = "ExcelIsFun". Thanks for the video Mike. That was great.
You are welcome for the Excel Trickery, Matt!!!!!!
Brilliant, thanks for this. I'd spent a good hour or so trying to figure out how to add All to the top of my dynamic dropdown list. I'm so pleased I found your video Mike. Please keep these great videos coming.
Glad it helps, Stephan!!!
I like the way you appended the "ALL" to the dynamic array. Very explicit. Thanks for sharing
You are welcome, Nono!!!
Really awesome Alt D L, spill operator in that, sequence in index for rows(;) perfect combination of logic and formula.
Glad you like it!!!!
Hi Mike. Awesome Monday madness with SUMIFS and Data Validation! Building on one of your past lessons, here is another way to create the sorted list of Products with ALL always appearing at the top: =SORTBY(IF(SEQUENCE(COUNTA(UNIQUE(fSales[Product]))+1)>COUNTA(UNIQUE(fSales[Product])),"ALL",SORT(UNIQUE(fSales[Product]),,-1)),SEQUENCE(COUNTA(UNIQUE(fSales[Product]))+1),-1). The fun never ends at ExcelIsFun :)) Thumbs up!!
PS - A shorter method is to change "ALL" to "" which would naturally sort to the top of the list (using "
Thanks, Wayne!! I'll add it to the download workbook so the Team is better : ) : ) : ) : )
Go Team!!!!!
@@excelisfun Awesome! Thanks Mike :))
@@excelisfun Go Team Go!!!
Masterpiece !!! Thanks Mike:-)).
I thought that we can use grand total row in Table to get " ALL" and then sort. I mean, type " ALL" in grand total row of the Product column. ( note that, there is one space before "ALL")
After sorting (inside formula) ," ALL" always will be the first item on our list .
=TRIM((SORT(UNIQUE(AnswerfSales[[#Data],[#Totals],[Product]])))
Bill Szysz, that is exceedingly clever : ) I LOVE IT!!!! I have added it to the download workbook : ) : ) : ) : ) Go Team!!!!!
This is a good trick. To add this, you go to Table Design then check the box that says "Total Row"
Brilliant stuff as always...
Glad you like it, SIMFINSO!!!!
You are the Excel expert! Thank you.
Just a guy having fun with Excel ; ) You are welcome, Mark!!!
This is some serious Beast Mode stuff! 😲
I like that method for getting ALL in the dropdown list.
Marshawn Lynch is from Oakland, you know ; )
I agree Oz! Mike goes Above & Beyond 🚀🚀🚀
Tthis ALL option is ideal for data validations #GoTeam !!!
@@spilledgraphics I LOVE the rockets : )
Marshawn Lynch, Beast Mode!!!!!!!!
Splendid!
Thanks for the great demo of both schools, Mike!
Yes!!! Splendid Both Schools : )
I always used asterisk instead of word ALL in drop-down list as I dint want to write a long formula. Learnt a lot from this video
I also have used asterisk most of the time : )
As always all ur excel videos are just awesome..I have personally learnt a lot watching at ur videos..looking forward to more such videos..keep up the good work sir..you are the real excel superstar
I am glad to help the Team learn lots of cool things about Excel and have fun doing it : )
Boom!All Super Cool Formulas Especially The LET Function Formula...Thank You Mike :)
You are welcome, darryl. Boom!!! : )
That's Amazing ... it's fun when i use some techniques in a report in my work .... then found Mike explains it in his channel ... but in a better advanced way ( i didn't use LET or "All")... thanks alot Mike ...you still the master
Just try to lead the Team to more fun : )
I did not know you can use the alt enter in formulas!!! That rocked my world.
Yes, it is a nice and clean trick : ) : )
I want to watch many times. I like the video.
Glad you like it, TRACEY!!!!
Thanks Mike. Happy Fathers Day.
Thanks original WRH!!!!! Happy Father's day to you too, kevin!!!!!
Incredibly clever. Thank you Professor!!!
You are welcome, Kevin!!!!
Dynamic data validation list is so amazing. Love it. Thank you Mike 👻👍👍
You are welcome!!!! I think I saw some ghost zero in this video. INDEX loves ghost zeroes : )
@@excelisfun I made my drop down list to be dynamic after watching your video. It's awesome. No more updating the list manually.
@@kiwikiow Awesome!!!!!!!
sir, your work is a masterpiece
Thanks, Wonjin!! Glad you like it : )
That's incredible :)
Glad you like it, Lucky!!!!
Always excel lent 👍 greetings from Bolivia.
Glad it was EXCELlent for you ; )
Another super video Mike💪💪.. Let function is amazing but i really love the old school tricks...
Yes, those are important old school tricks that are so useful we should also call them new school ; )
@@excelisfun True :)
Thanks Mike. Amazing!!! An interesting scenario and a wonderful way of how you got around it. Always learning. : ) : )
We are always learning. Lucky for us!!!!
Formulas are the most fun too : ) : )
So much fantastic and we'll explained information in here, but 9:50 alt+enter just made my life so much easier. Thank you for your content!
You are welcome for the content!!!
Mike, another great video, go Team!
Go Team!!!!!!
Thanks a lot for Great Session !
Awesome as always.. learnt so much from you Mike.. thanks for these videos.
You are welcome for the videos, Vipin!!!
Something different and unique!!! 🤩🤩🤩
This is the USP of your videos. Love this trick! ❤️❤️
What does USO mean ?
Glad you loved the trick : )
@@excelisfun unique selling proposition
@@deepk82 Was I supposed to know that? lol
@@deepk82 Thanks for the clarification!!!!
My head hurts; so much to process... Awesome as always!
EXCELlent video as always. Thanks Mike
You are welcome, Fellow Teacher Syed MM : ) : ) : ) : )
awesome, i don't know where to use it but👍
🤣
@@zeroskiing It is funny : ) : )
Glad it was awesome-but-no-use yet : )
Felt old when you referred to SUMIFS as "old school". I still think of it as new.
Me too. I still remember when it was brand new in 2007!!!! Do you remember when it came out then?
@@excelisfun Sure do. It made my life significantly easier. No more concatenation columns.
@@thomroethke522 Fewer SUMPRODUCT, D functions, and that old and crazy SUM IF add-in wizard to build your array formula for you : )
Superb!!
Another awesome video Mike, Thank you = i LET you amaze me ;)
Yes!!!!!! Glad that the LET is awesome : ) : )
THANK YOU THANK YOU THANK YOU!!!!!
Thanks for your explication
You are welcome!
Mike u always great 👍
Glad that I can help you : )
Thank you 😊
You are welcome!!!
Nice trick, Mike, good stuff!
I like the intros the best : ) : ) : ) : ) Funny, I feel like a latte right now lol
At 1:00 - the quickest way to Paste Values is: right-click, V, or: Menu+V, or Menu,V.
You know: that Menu key on the keyboard that produces the context menu (just like right-click does).
Do you see the beauty of this? CTRL+V vs. Menu+V? Just beautiful! :-)
@@GeertDelmulle I definitely like that right-click key : )
@@excelisfun LOL! Yeah, I could use one myself, right now. :-)
@@GeertDelmulle Only we are in the know lol
It's a great idea of having ALL in drop down list. It would be even greater if Excel allowed to enter formula directly to Data validation window or access unique list from Power Query and return it in drop down list
I agree. It would be MUCH better !!
Excellent
Glad it is EXCELlent for you, kishor : )
Old school is love
Wow. Didn’t know that you could get all rows in the INDEX function if you type a zero in the row argument. I learn something new everyday. Many thanks 🙏 for the beautiful LET trick. By the way, do you have any course on Statistics from basic to advanced? I am looking for such a thing and since I really like your didactic abilities, I was wondering if you had something…
ME TOO ! wow!. To your question, he does! check out the playlists section José! Saludos!
@@spilledgraphics many thanks 🙏. Btw I like your name… Recalc or Die!! 😂😂 let’s recalc just in case 🤣
@@josecarlosconejo5724 🤣😂🤣😂🤣😂🤣 🙌
INDEX with a zero or omitted: I have that trick in at least 100 other videos. It is a very common trick. Useful too.
Yes, I have a statistics class and an analytics class. You should watch my 2 minute intro to the excelisfun channel which shows how to find all my classes: ruclips.net/video/l1-1aVgFth4/видео.html
Statistics class: ruclips.net/p/PLrRPvpgDmw0ngx_uPhvasTbOWLOztsaBj
Wildcards, very amazing, is it possible to sort the drop-down data validation list using the old school method, thanks for the amazing video🙏🙏🙏
Yes, but is THE most complicated formula in the old school. I wrote a whole book about array formulas and it took 5 chapters to explain the sort formula. Here is a video about it: Ctrl + Shift + Enter: Excel Array Formulas 20: Extract Unique Lists & Sorting Formulas, ruclips.net/video/J6yeTUFkLzQ/видео.html
Now that VSTACK is available, wouldn't the solution be =VSTACK("all",SORT(UNIQUE(fsales[Product]))) ?
Great
Glad it is great for you!!!
Thanku sir for this
But I tried this on google sheets the spill operator show errors. Will you plz type formulla how to get row no increased in google sheets.
Cool video as always but that LET function....you got to be a programmer to use it ;P variable in function... that's sick
Yes, it is sort of like programming : ) LET Excel be even more fun with programming : )
I can tell you: once you get used to using LET, you won’t want to do it any other way: just too beautiful!… :-)
@@GeertDelmulle I hope so;P I have no Access to Excel 365 yet and I only slighlty licked VBA . I have to get used to it. More videos with LET function are welcome xD even if I don't have those new features I still love watching those mastermind tricks xD
Nice append trick, but overkill in this case. There is a little known trick for extending the dimensions of a dynamic array, simply by using the range operator (colon). In this example, with the unique list located in cell L9#, all you need to do is type “ALL” in cell L8, then append the two simply by referencing the range =L9#:L8
That would be a mind blowing trick if I could get it to work. When I have a dynamic array in a formula I can't type a colon directly after it. When I try to type colon to complete formula, the colon does not appear. How do you do this?
Since this video we have been given new function by Microsoft. So now we can: =VSTACK("ALL",SORT(UNIQUE(AnswerfSales[Product])))
Yes VSTACK is great, but I’ve never had a problem with the range reference myself. Just sat down to test again, and it works fine. To use this type of reference with Data Validation, you would need to define a name in Name Manager, and it would refer to =Sheet1!$L$9#:Sheet1!$L$8. You can then use the defined name as the list source.
You could also try a LAMBDA function… =LAMBDA(range1, range2, range1:range2)(L9#,L8). Name it MAKERANGE or EXTEND if you want to add the function to Name Manager.
@@davidabuang I finally got it to work!!! Now I see that it works when the ALL and data are directly next to each other. Very cool trick. Thanks for the hot tip : )
😳🤯😱 (me, speechless)
Speechless maybe, but having fun yes!!!!!! : ) : ) : ) : )
@@excelisfun oh, yeah! In your channel, always lots of fun!
old School vs. New School ????? no way ! 😵🤯😵🤯😵
Really "" ??? I struggled with this many years ago! 😝😝 thanks Mike !!! #GOTEAM !!!
Last two things Mike: the use of 0 on a INDEX? what!? and loving those 💥"BAM"💥 moments on the video 🤘😅
The two schools together to make Excel MUCH more fun!!!
@@spilledgraphics Yes, NOT empty is a good criteria trick to have up our sleeves : )
@@spilledgraphics Bam!!!!!! Yes, the INDEX with a zero or omitted: I have that trick in at least 100 other videos. It is how we lookup a field or column.
Amazing and informative. Thanks But Mike, I felt you are not energetic as always.
Am I right? Also I miss your bonus in tutorials.
I am getting old and tired, but I keep on going lol
P.S. There were lots of bonuses, like the "", but I guess I was too tired to add the extra animation LOL
BTW, you did pick up my lack of energy. You are right. And in fact I was in very bad back pain all day, but still kept going and filmed the video. I ended up in the emergency room this night due to the pain.
@@excelisfun I'm really sorry to hear that, hope you get well soon and back with full of energy as always.
We all need you in health.
I will pray to have you healthy very soon.
@@Softwaretrain Thanks for the positive vibes and for noticing in the first place : ) It is great to be on the Team with you : ) : )
@@excelisfun Dear Mike,
hope you have recovered from back pain. You are an icon of knowledge sharing for me.
hi can you help me, it doesnt work for me
Q: UNIQUE() is not case sensitive; UNIQUE({"A", "a"}) returns "A" (or is it "a" - can't remember). How to get a formula that returns {"A", "a"}?
Wouldn't a pivot table be a much more elegant solution?
Yes. But for people who are doing what if analysis and want an all option, this is perfect!
Interesting, but too much work for me just to hide the Grand Total when the criteria is a single product! I like 3 colums in the results area: Product; Product Total; Grand Total. (The drop-down list is below product; SUMIFS below Product Total; SUM below Grand Total.) Only difference that I see is that the value for Grand Total is constantly visible rather than just when you select ALL. It's easy to HIDE the GT column if seeing it annoys the user!!
I like that. That is a hot tip: just add a grand total cell. But, I am surprised how this question about adding "ALL" as a condition has come up numerous times in the last 20 years. So I guess people like to do too much work lol
@@excelisfun I haven't tried, but can you do something with a dependent drop-down list that will select SUM when the product is ALL and SUMIFS otherwise?
@@richardhay645 That was the first formula that I did in this video: IF(F4="ALL",SUM,SUMIFS), But I had a dropdown list, not a dependent dropdown list.