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