Append “ALL” to Top of Sorted Unique List for Data Validation List for SUMIFS Function. EMT 1740
Download Excel File: https://excelisfun.net/files/EMT1740.xlsx
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
Видео Append “ALL” to Top of Sorted Unique List for Data Validation List for SUMIFS Function. EMT 1740 канала ExcelIsFun
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
Видео Append “ALL” to Top of Sorted Unique List for Data Validation List for SUMIFS Function. EMT 1740 канала ExcelIsFun
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
![IF, FILTER, SUMIFS or COUNTIFS for OR Logical Test Aggregate Calculation? EMT 1746](https://i.ytimg.com/vi/bQF62eQMYlk/default.jpg)
![Amazing Excel FILTER Function + Trick to Rearrange Column Order](https://i.ytimg.com/vi/ZCQAweoAdOw/default.jpg)
![SUMIFS with Dynamic Data Validation List & Conditional Formatting for Row. Excel Magic Trick 1739](https://i.ytimg.com/vi/SDfb-cdDDb4/default.jpg)
![Double FILTER Criteria "Top N Values of 2nd filtered Criteria" Excel Dynamic Arrays](https://i.ytimg.com/vi/hn2qSx1nivo/default.jpg)
![TOCOL & IF Functions to Convert Frequency Distribution into Original Column Of Data. EMT 1789](https://i.ytimg.com/vi/KxOrJagtaRw/default.jpg)
![Day of the Week Sales Report: Helper Column, PivotTable, Formulas, Array Formula? EMT 1742](https://i.ytimg.com/vi/s7qi0Kwb_IY/default.jpg)
![Excel tutorial: SUMIF and issue with DATA SORTING. How to fix it?](https://i.ytimg.com/vi/FfzR2ei_oNg/default.jpg)
![Lookup Last Price In Column Based On Criteria In Other Columns. LOOKUP or XLOOKUP? EMT 1751](https://i.ytimg.com/vi/rud433VTwOU/default.jpg)
![PivotTable Calculated Items and Solve Order](https://i.ytimg.com/vi/ZJh9DjxTg5w/default.jpg)
![Excel SWITCH Function, is it really as good as they say? You might be surprised!](https://i.ytimg.com/vi/oJ-RwnZ_uE8/default.jpg)
![](https://i.ytimg.com/vi/8qpVKiMiWmU/default.jpg)
![Create a searchable drop down list in Excel](https://i.ytimg.com/vi/vkPoViUhkxU/default.jpg)
![NEW! SEARCHABLE Drop Down Lists in Excel with ZERO Effort!](https://i.ytimg.com/vi/3Qh5nUmCVd8/default.jpg)
![Excel Data Validation using Dependent Lists](https://i.ytimg.com/vi/u2aTuK7fJxw/default.jpg)
![Excel SUMIFS: Sum Alternate Columns based on Criteria and Header](https://i.ytimg.com/vi/2odvwY_mWN8/default.jpg)
![Create an Interactive Top N Report in Excel (includes duplicate values) with Filter Function](https://i.ytimg.com/vi/gmzGQjaIh_k/default.jpg)
![Data validation without duplicates - Excel Tricks](https://i.ytimg.com/vi/4Rv5twNqrGM/default.jpg)
![Calculate Total Sum From A Unique List | Excel Tutorial](https://i.ytimg.com/vi/p1_iWiMful4/default.jpg)
![5 Excel Functions YOU NEED in 2021 & 2022](https://i.ytimg.com/vi/_EWcAR_Hkvg/default.jpg)
![Excel 2010 Magic Trick 818: Extract Records For Each Country To A New Sheet With Formula](https://i.ytimg.com/vi/pHdftPxQsVE/default.jpg)