Append “ALL” to Top of Sorted Unique List for Data Validation List for SUMIFS Function. EMT 1740

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

[wayneedmondson]

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 "<ALL>" which would naturally sort to the top of the list (using "<" or any character that would sort before "a" or "A" in the Product column): =SORT(IF(SEQUENCE(COUNTA(UNIQUE(fSales[Product]))+1)>COUNTA(UNIQUE(fSales[Product])), "<ALL>", UNIQUE(fSales[Product])))

[mattschoular]

=LET"Excel Trickery" = "ExcelIsFun". Thanks for the video Mike. That was great.

[SJV]

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.

[deepk]

Something different and unique!!! 🤩🤩🤩
This is the USP of your videos. Love this trick! ❤️❤️

[nonoobott]

I like the way you appended the "ALL" to the dynamic array. Very explicit. Thanks for sharing

[traceylu]

I want to watch many times. I like the video.

[shaileshcastelino]

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

[rrrraaaacccc]

Always excel lent 👍 greetings from Bolivia.

[mahipaljain]

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

[kiwikiow]

Dynamic data validation list is so amazing. Love it. Thank you Mike 👻👍👍

[canirmalchoudhary]

Really awesome Alt D L, spill operator in that, sequence in index for rows(;) perfect combination of logic and formula.

[sideshowbobby]

I did not know you can use the alt enter in formulas!!! That rocked my world.

[ivanmamchych]

Splendid!
Thanks for the great demo of both schools, Mike!

[KevinPGA]

Incredibly clever. Thank you Professor!!!

[lucaviglio]

Another super video Mike💪💪.. Let function is amazing but i really love the old school tricks...

[BillSzysz]

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]])))

[OzduSoleilDATA]

This is some serious Beast Mode stuff! 😲
I like that method for getting ALL in the dropdown list.

[DavidASix]

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!

[HusseinKorish]

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

[darrylmorgan]

Boom!All Super Cool Formulas Especially The LET Function Formula...Thank You Mike :)