Download Excel File: https://excelisfun.net/files/EMT1743.xlsx
Learn about aggregate calculations with conditions and criteria. Learn about an AND Logical Test, and then see how the FILTER function can help with OR Logical Tests and advanced conditional calculations.
See a review of an AND Logical test using SUMIFS, COUNTIFS and similar functions. Learn how to do an OR Logical Test inside SUMIFS and COUNTIFS functions when there is ONLY one column from the table involved. Then see that there is trouble if you try to do an OR Logical inside AVERAGEIFS function, but the FILTER Array Function will come to the rescue. Then see how to use the TEXT function inside FILTER Function to make aggregate calculations inside the AVERAGE function. Then see how to use FILTER .
1. (00:00) Introduction
2. (00:21) Review of an AND Logical test using SUMIFS, COUNTIFS, AVERAGEIFS and more.
3. (00:52) How an OR Logical Test works on a single column.
4. (02:04) OR Logical Test across one column using SUMIFS and COUNTIFS functions.
5. (03:45) Trouble if you try to do an OR Logical inside AVERAGEIFS function, but the FILTER Array Function will come to the rescue.
6. (04:14) OR Logical Test on One Column is equivalent to “Compare Two Lists” Logic. Use XMATH and ISNUMBER functions
7. (06:10) TEXT function inside FILTER Function to make aggregate calculations inside the AVERAGE function for a Day of the Week condition. Formula from Teammate: Stein J. Hågensen.
8. (07:37) How an OR Logical Test works on more than one column.
9. (08:38) All Logical Worksheet Functions interpret any non-zero number as TRUE
10. (08:53) FILTER Function to run OR Logical Test across more than one column.
11. (09:12) OR Logical Test uses a Plus Operator.
12. (09:28) Order of Operations in Excel Worksheet Functions.
13. (10:01) New and upcoming book: The Only App That Matters!
14. (10:15) Summary, Closing and Video Links