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

our goal is to change the condition to a different product name get the sumifs total but if we select all we want to add the whole column and when we add new data with new products to our excel table we want our drop down to still have all at the top but we want it to include all the new products now we're going to see some amazing old school sumifs tricks and we'll see some new school microsoft 365 tricks [Music] now the first thing we have to do is get a unique list from this column and the cheap and easy way is just to copy everything in the column control c and then off to the side control v i want to paste as values only so i'm going to open the smart tag with control and paste values is v now to get a unique list i'm going to go to data data tools remove duplicates i do not want to expand the selection so i'm going to continue with the current selection remove duplicates click ok ok and at the bottom i'll just type all now i want to add data validation drop down list so data and there's the button but let's use the keyboard alt d l tab to get to allow l to get to list tab to get to source and we'll highlight click ok so now we have our drop down with all at the bottom now we can use the sumifs function to add with one condition sum range we'll select the whole sales column comma criteria range select the product column comma criteria we'll use aspen that cell reference f4 close parentheses and enter so that'll work for any of the products but once we select all there's no all here so sumifs correctly delivers zero but let's f2 put it in edit mode after the equal sign we'll use the if function and say hey if that cell right there equals in double quotes that all text comma the value if true well i want to add everything so i'm going to use a second function adding all the sales comma and then value if false will just run some ifs close parentheses and enter so now we have it working aspen it gets the right total all it gets the right total now there's some other ways we can do this if you had an empty cell here well some is going to add everything here no matter what's in the product column but maybe you didn't want that and you might have a formula that delivers a zero length text string now we can exclude both of those if we want so we do sum ifs but in criteria one that's where we'll put the if function so i'll say if f4 equals all then because this is in criteria one in value of true i need a condition that will pick out all text that's one or more character and that means in double quotes we have to use wild cards question mark means exactly one character asterisk means zero or more characters so we put that in double quotes and it will only add when there's one or more characters that's the value if true comma the value if false will be f4 and that's our formula now for some reason you really did want this zero length text string then we don't want to say one or more we want to get rid of the question mark and just use the wild card for zero or more characters and now that one will work that formula is adding everything except for the actual empty cell and there's still one other situation what if you had not text but a number and you wanted to count it then you use the condition not empty double quote less than greater than and double quote now here comes the fun part of the video because if you have microsoft 365 excel instead of manually creating this data validation list with the word all we can use a formula to create the unique list that'll see any new data that's added and it will always have all at the top now we're gonna do this in a few steps and then we'll mash it all together right here so the first step is to get a sorted unique list so we'll use the amazing dynamics build array function sort and then unique inside of that pointing to the product column close close and when i hit enter we get a spilled unique list but notice up in the formula bar all the cells below the top cell are grayed out the formula only lives in the top cell now there's four items here and we really need five items that means we're going to have to trick our formula and convert this from one column by four rows to one column by five rows and we can start that trickery with the sequence function we need to know how many rows are from that spilled array and if i highlight it the pound or hashtag spilled operator says get everything that spills from l9 close parentheses now i need five rows so i'm going to add one comma we don't need columns comma and we're going to start at zero close parentheses when i hit enter this gives me 0 1 2 3 4. now the significance of that 0 is because we are going to use index and index is notorious comma if in the row number argument you give it a 0 that means it'll get all the rows so this will in essence look up and deliver all four rows now if we put this sequence of number into that row number argument the zero gets four items but the one two three four that will get one two three four and because we're going to tell index to get the one after it already pulled the zero it'll replace the items here to see this in action we'll use index and the array will be the spill dynamic sorted list and there's row number close and when i hit enter that's our trickery we have a duplicate value at the top but now we have five rows of data and from that we can say if sequence gets a zero then please give me all and all will appear right there otherwise deliver what index has and it will deliver all the rest so after the equal sign i'll say if i actually need control c that control v if anything in that sequence is equal to zero comma then please give me all comma otherwise the value if false index now remember index is delivering that duplicate at the top but because we use the zero all will already be there and so that's how we append all close parentheses to the top of our dynamic list if i type a s here bam just like that it's working control z now we have lots of duplication here we use sequence twice and we use this unique list three times so anytime you have duplicate formula elements that's the perfect time for the let function now i want to copy this using control c c that will copy it and open the clipboard enter and then here ctrl c so i have these two things stored up in my clipboard equals let and let is amazing because it will allow us to define variables and then use those variables the first argument is name one this is where we get to name the variable sorted unique list comma and the formula element for s-u-l i'll come over to the clipboard and click comma calculation or our second name and for us we'll call this variable rows comma and the value will be the sequence except for right there we get to use our variable s-u-l all right so so far we have two variables s-u-l and rows comma and now we make a calculation if rows and watch this when i type rows there's the icon for the variable just in this one let formula tab are you equal to zero comma if you are then all otherwise index looking up from sul tab with the rows variable close parentheses close parentheses and close now let's make this easier to read after the first variable alt enter after the second variable alt enter and that is beautiful xx bam control z now we can come over here alt d l tab l tab and our source will be j9 then we type the spilled range operator click ok and now when we change the product it's working when we come down and copy new data paste it to the bottom of the excel table uh-oh this one up here did not update but bam there's the new product bellen and all is at the top all right so we learned about how to use all as a condition in some ifs we saw a formula that will add everything no matter what one that uses the text criteria one character or more zero characters or more and the condition for not empty and then we saw this beautiful let formula all right if you like that video be sure to click that thumbs up leave a comment and subscribe because there's always lots more videos to come from excel is fun alright we'll see you next video [Music] you

Channel: ExcelIsFun

Views: 15,905

Keywords: Excel, Highline College, Mike Girvin, excelisfun, excel is fun, excel fun, excel magic, Excel Formulas, Excel Functions, formulas and functions, Excel Magic Trick 1740, EMT, EMT 1740, Microsoft 365 Excel, Office 365, Excel 365, Dynamic Dropdown List, SUMIFS function, ALL Criteria, SUMIFS with ALL condition, How to get SUMIFS to add with ALL criteria, Append Columns, Excel formula to append columns, LET function, Append ALL to top of sorted unique list, Data Validation List

Id: Lo0k73rdLN4

Length: 11min 18sec (678 seconds)

Published: Mon Jun 14 2021