Download Excel File: https://excelisfun.net/files/EMT1739.xlsx
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