SUMIFS with Dynamic Data Validation List & Conditional Formatting for Row. Excel Magic Trick 1739
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1739....
Learn how to create the most awesome SUMIFS function solution possible. Add a dynamic data validation dropdown list that updates when new products are added to data set and which will highlight records in table that match the condition that SUMIFS uses for adding.
1. (00:00) Look at dynamic finished solution
2. (00:27)
3. (00:37) Microsoft 365 Excel makes this solution easy. Video links hsown for solution that you can use if you do not have Microsoft 365 Excel.
4. (00:50) SORT & UNIQUE Dynamic Spilled Array Functions to extract sorted unique list of product names to use in Data Validation Dropdown List feature.
5. (01:24) Explain how Dynamic Array formulas work.
6. (01:38) Dynamic Data Validation Dropdown List feature with Spilled Range Operator to refer to Spilled Dynamic Array Formula.
7. (02:24) SUMIFS function to add sales for the selected product.
8. (02:47) Conditionally Format Rows in Data Set that match the condition for adding in SUMIFS function. Learn how to use a Logical Formula with Mixed Cell Reference to Conditionally Format the row.
9. (04:48) Add new data to Excel Table and the SUMIFS function, the conditionally formatting and data validation dropdown list are all updated correctly.
10. (05:17) Summary, Closing and Video Links
Loved this one!!
Darlene!!!!!! : ) : ) Do you have Microsoft 365, so you can have all this awesomeness ?
ALWAYS BRILLIANT
Glad you like it, rrrraaacccc80!!!!
Loved this lesson
Yes!!! : )
You are awesome, short video but comprise pearl tips and learnings.
Glad you like it and thanks for the link from the other video : )
Thanks Mike!
You are welcome, Luciano!!!!
Thanks again, Mike, great explenation! I can use this one for other things, too. Greetings from the Rhineland, Germany! (for sure you get a 👍)
Thanks, Dirk!!!! I send a greetings from Seattle, WA to Rhineland!!!
Finally a dynamic drop down list ! Thanks Mike, brilliant.
Yes, M365 makes life so easy!!!!!
Excellent sir you are excel magician
Just having fun with Excel : ) : )
Very useful.Nice.Thanks for posting
You are welcome, SIMFINSO!!!!
Awesome: You & Office 365
Yes, Microsoft 365 is THE best : ) : ) : ) : )
Merci beaucoup pour l astuce 👍
: ) : ) : ) : )
Thanks. Someday, I will actually be able to employ the lessons in applications I need.
You are welcome, Geoffrey!!!
Just started as a controller over three companies. I cannot begin to emphasize the usefulness of your mspdta playlist along with all of your videos! Thank you so much for doing what you do and I will continue to promote your videos
You are welcome, T Rosen!!! I am glad to help.
Thank you!
You are welcome, Santiago!!!
Yes sir...Awesome sums it up. Thanks Mike
Thanks for the awesome sum, Matt!!!!!
I love this.
It is always exciting to train and see peoples' eyes open wide in amazement...
Totally, totally true. The new Excel makes our job as trainers much more fun, and efficinet too : ) : )
For even more fun, reference the same drop-down cell in the "to Include" argument of the DA FILTER function to generate the list of records that are highlighted by the conditional formatting!
That is JUST TOO COOL!!!!!! I can't wait to show that in a video sometimes soon : ) : ) Thanks for the hot "Richard Hay" tip !!!!
These spilled arrays actually made me spill my cup of tea! ☕️ 🤣 Mile - you are the Excel Jedi Master! Thanks for all you do, Sir! 👏🏻 👍🏻
You are welcome, David West!!!! How did you spill your tea?
Amazing video ❤️
Glad you like the fun, Gokul!!!
Thanks Mike. I am a bit late on this one, although I saw it from my Mobile a few minutes after you posted. Yes Formulas RULE!! : ) : )
Thanks, Formula Guy John : ) : )
Nice, just perked up my reconciliation tables with that formatting tip :) now to get the cells to flash... :)
Yes!!! I love to hear that. I have been using the Row Conditional Formatting trick for my bank and credit card account reconciliation forms for the past 22 year!!! Old School Tricks still work like a charm : )
Love it!
Glad you love it, Color Mile-hi!!!!!
Congratulacions man, you are a genious in Excel. Go ahead 👍
Just a guy having fun in Excel : )
Great Mike! The fun and utility of both modern and legacy EXCEL features is endless. Thanks for this tasty Tuesday example of how to combine them together for a great result! Always good learning and good fun at ExcelIsFun :)) Thumbs up!!
I love that: tasty Tuesday modern legacy fun!!!!! Thanks for stopping by as always, Wayne : )
Thank you Mike. You make complicated tasks easier to understand.
You are welcome for the "making complicated things, less complicated"!!!!
Simple, clear and to the point.... just legendary Mike in action :-))
Your coaching skills are beyond my imagination.
I have been missing you soooooooooooooooooo much, Bill Szysz!!!! I love you, and am happy to see you boomeranging back : ) Thanks for your kind words: but me, I am just having fun with Excel lol
Certain features looks so simple but only we come to know when some experts like you explain with a video. Great video and thanks for sharing
You are welcome for the share, Lakshmipathi!!!
This is very useful. Thank you so much.
You are welcome so much, Paulo!!!!
As always , an EXCELlent video, fun with dynamic spilled array and I like the new intro.
Thanks, Fellow teacher Syed MM : ) : ) : ) : )
Excellent video Mike. You guys never fail to impress. I have to admit that I have learned a great deal following this channel. Stay safe. Cheers !
Glad you have learned a lot, Sachin!!!!
Simply amazing! Boom!
Boom! Glad it is amazing for you : )
Boom! Simply Awesome...Thank You Mike :)
You are welcome, darryl!!!!! Boom!
Always Amazed with your IDEAS Combinations, Simple & Efficient at the same time, always thank you for your efforts.
Glad you like it all!!!!!
Two amazing things in all of your training,
1. Great explanation and really informative
2. Answer all comments
No doubt every Excel user should watch your tutorials even professionals.
Thanks for your all effort.
Thanks for your kind words!!! Coming from a fellow expert trainer like you, Software Train, that means a lot : ) : ) : ) : )
This is Amazing ... i always enjoy your simplicity of explanation ... thanks Mike
Making complex things less complex is fun : ) : ) : )
So cool Mike, another excellent video from the master.
Glad it is cool for you, Chris!!!! It was cool to make too.
Awesome
Glad it is awesome for you, Anthony : )
Mike, all of my spreadsheets work so much better because of your videos...
@@anthonyverdin6743 Yes!!!! I love to hear that. That is why I post : )
Great little video!!
That’s some “spilled array fun” against the wall and some excel(is)fun emerges magically.
I saw what you did there… :-)
BTW: indeed, working with an intermediate helper column to assist in the creation of a dynamic dropdown list a useful technique.
(Too bad that range reference box does not allow for formulas involving dynamic arrays, allowing us to avoid the helper column.)
Ya, that is true. But I'll take it any way : ) : ) : ) : ) With this new helper column, it is about 20 times easier than in the before-Microsoft 365 days!!!
"a little fun with dynamic spilled array"
Only a little? You, Sir, underestimate just how nerdy and excited some of us are about dynamic spilled arrays. :)
As always, thank you for your continued effort in educating us all.
You are welcome!!!! I am nerdy and excited about dynamic spilled arrays too : ) So much so that I wrote a whole book about array formulas, but the old school way, way back in 2012 : ) : )
@@excelisfun Can we expect a revised version of the book anytime soon?
By the way, fantastic video and insight as always and, all for FREE! Unbelievable value. We cannot thank you enough.
@@frankabacus7375 Yes for the updated new book!!! My new book that I am writing has a huge section on Array formulas. But actually, the whole first 400 pages is about array formulas because the new Excel Calculation Engine treats everything as an array formula. I should finish writing in in the next couple of months, then it is about a 6 month editing and publishing process. So probably early next year.
You are welcome for the free context. My goal for the last 13 years at RUclips has been to provide free Excel education to the world : )
@@excelisfun 1 year! That is long, but we will wait. Your are crushing that free Excel education goal with so much energy. Keep it up. Thank you and thank you
Excel dynamic spill technology is just terrific. Good video, Mike. Fun trick: to select all the data in a Table, hover the mouse carefully over the leftmost header (Date, in this vid) and it'll turn into a diagonal right-down arrow. Click then and all the records in each field will be selected.
That is a hot tip!!! I have demonstrated that in many videos. You have not watched all 3,300+ videos that I hvae posted, DRSteele? lol ; )
@@excelisfun I sure have! Some of them dozens of times. No kidding. I was just giving the tip to your newcomers.
@@drsteele4749 12*3300 = A LOT of watches lol Thanks for the hot tip for the newcomers. It is actually one of the sooooo many reasons to use Excel Tables.
Really nice video Mike and also the intro is quite cool. Is that you with the paint ;)
Thanks, Mike! I have made a few forms using the same method this year, and it's been a game changer. I wish the data validation allowed me to use the dynamic array formula directly in the "source" field (or to use a "name" with a dynamic array formula assigned) for a cleaner look, but I tend to be too demanding... 😏
Isn't that the truth!!! But, I remember doing the crazy huge array formulas to extract unique lists and sort, so a little list off to the side before using data validation is ok with me. BTW, did you ever read the Ctrl + Shift + Enter Array Formula book I wrote, back almost 10 years ago? I had multiple chapters just to explain how those old formulas worked lol
Working with data is made easy with office 365 spilled arrays, I've love the intro and outro of the video, spilling Excelisfun arrays on the wall😂😂😂
Yes!!!!! excelisfun spilling arrays lol
⭐️⭐️⭐️⭐️⭐️
Thanks, Redha!!!!!
Don't have words to express my feelings 😀 1 step ahead woth data validation (dynamic data validation)
I am glad that you like the video!
I have been watching your videos for 6 years i probably hear your voice more than my university teacher :) And i'm curious which region you are from i can't figure out from your accent :)
I am from Oakland, CA but live in Seattle, WA. Glad that I can help for 6 years : )
Great tool! Much appreciated! 👍
I use Dynamic Data Validation Lists to standardize data collection in empty form, faster and cleaner.
I see potential for the SUMIFS function with conditional formatting. Should TRIM be added to avoid "duplicate" in the unique list?
I also like to use AGGREGATE in D1, D2 and D3 (lowering the table to D4) with Mean, Standard Deviation and Sum. Than I just use the filter in C2 to choose the item I need to perform the calculations.
Sure, you can use TRIM if there are empty cell issues. That is a great idea : ) AGGREGATE has been one of my favs too, since 2010!!!!
@@excelisfun Could AGGREGATE be implemented in G3 instead of SUMIFS to also have a dynamic calculation?🤔
One could choose the item and calculation type they need.
@@Muuip If I am adding with conditions, I would always use SUMIFS if possible because it calculates significantly more quickly than a formula with an array operation, like AGGREGTAE does. I did extensive testing back in 2001-2012 and wrote a book about array formulas, and SUMIFS was the fastest.
@@excelisfun Excellent! Thanks for the Info! 👍👍👍
@@Muuip : )
You're magic as tour tricks, but we need more magics with the new Lambda function
You are right. I only have 2 videos about Lambda: ruclips.net/p/PLrRPvpgDmw0m0ZfgxxFf9co6EB2cr_Jyq
I will make more later in the year for sure : ) : )
Can we make own sort list while using unique function?
I have a long list and wanted to be able to type in a few letters of an item to narrow down the drop down list. I used the formula:
='Industry PTs'!$O$4#
for the Data Validation list. That cell has the following formula:
=FILTER(StockData[Symbol],ISNUMBER(SEARCH(Dashboard!N1,StockData[Name '[Symbol']])),StockData[Symbol])
where StockData is a table on another worksheet, and Dashboard!N1 is the cell where the data validation is. I should mention that StockData[Symbol] is a STOCK Data Type (Data -> Data Types) where a single cell is worth a value like:
APPLE INC. (XNAS:AAPL)
It works great, however when I tried to reproduce this in another workbook I couldn't get it to work. There is a [Name] column and [Symbol] column in the Stock Data Table, but have no idea where "StockData[Name '[Symbol']]" comes from, and when reproducing it in another workbook with the same worksheet and table names it fails. I probably got the idea from one of your videos, but can't find it now. Would love to see an EMT on this. Thanks!
I am not sure. Try posting detailed question to: mrexcel.com/board
Professor, the whole treatment requires that the initial table has been declared and formatted as a table?
Yes, nothing would update if you did not use the Excel Tables. Excel Tables are the real magic : ) : ) : )
how do i do it if my table in different sheet
If I have a data set where I make pivot tables. The pivot tables have two rows. I then need to do a lookup from multiple tables. I tried Vlookup with a nested switch. Can you make more videos doing lookups from pivot tables? It’s tough because some rows are empty in the pivot table. Please help me lol
I am not sure. Try this good Excel question site for back and forth dialog to get Excel solutions: mrexcel.com/board
But if remove the reference Data so it does not work(there is any possible if we remove reference data it should work)please update me.
hi, i don't have 365 .
but i think that in the validation window instead of choosing the spill array we could have =sort(unique(indirect("fsales[Product]"))).
And why would you do it that way?
@@excelisfun because i don't like to have helper column.
@@jrcryo Oooo, I see... But Spilled array formulas like SORT and UNIQUE are not allowed in the Data Validation text box : ( I don't know why Microsoft programmed it that way.
@@excelisfun ah ok :-/ thanks for your answer
@@jrcryo Thanks for your cool comment too. I wish it did work : )
Sir, is it possible in office 2016? I mean to conditional formatting to update automatically?
Excel Tables and Conditional formatting works the same. But the SORT and UNIQUE function does not work.
It is complicated in any Excel that is NOT Microsoft 365. Here is how to create unique list in Excel 2016: ruclips.net/video/3u8VHTvSNE4/видео.html
@@excelisfun Thank you sir for your reply instantly.
@@kartickchakraborty9135 : ) : )
Is there is a way to know what is the total sum of ALL the products by selecting product 'ALL'?
Are you asking about DAX?
Are you asking about a data validation drop down list? If yes, you are NOT going to believe: that is the next video I planned to do. There are a few ways to do it.
A formula like this will work: =SUMIFS(fSales[Sales],fSales[Product],IF(F3="All","?*",F3))
But the trick is how to create the data validation list ; )
@@excelisfun
Yea, that is what I'm asking for. Eager to see the solution.
@@LotfyKozman You must have been reading my mind ; ) Next video is early next week.