Download Excel File: https://excelisfun.net/files/EMT1819.xlsx
The best Excel formulas are not always what they seem. In this video: learn the best ways to create counting formulas in Excel using COUNTIFS, FILTER and SUM( IF ). Learn both Microsoft 365 Excel formulas and formulas that will work in any version of Excel.
Topics:
1. (00:00) Introduction
2. (00:14) COUNTIFS for counting customers who paid Cash. (Single Condition)
3. (01:07) AND Logical Tests. OR Logical Tests. And what is required in counting formulas.
4. (01:32) COUNTIFS for counting customers who spent less than $50 and paid with PayPal. (AND Test). Learn about two ways to enter comparative operator.
5. (03:18) OR Logical Tests and the trouble with them. How to not get in trouble.
6. (04:09) COUNTIFS & SUM functions for counting customers who paid with Visa or Mastercard. (OR Test, No overlap: Mutually Exclusive Categories))
7. (05:41) FILTER for customers who paid with Visa or spent at least $350. COUNTIFS alternative formula too. (OR Test, overlap in categories: categories are NOT Mutually Exclusive)
8. (09:45) SUM ( IF ) for customers who paid with Visa or spent at least $350. (NOT Mutually Exclusive)
9. (11:00) FILTER for customers who paid with PayPal or spent between$50 and $100. FILTER and COUNTIFS alternative formula too. AND Logical Test inside an OR Logical Test.
10. (12:32) Differentiate between the complex filters: 1) AND Logical Test inside an OR Logical Test, 2) OR Logical Test inside an AND Logical Test.
11. (12:48) COUNTIFS & SUM functions for customers who paid with PayPal or Apple and spent exactly $100. SUM ( IF ) alternative formula too. OR Logical Test inside an AND Logical Test.
12. (14:43) Summary of all counting methods
13. (15:48) Closing & Video Links

#excel
#excelisfun
#excelformulas
#excelfunctions
#dynamicarrays
#array #arrayformula
#statistics
#excelsolutions
#joint
#probability
#probabilitytricks
#mikegirvin
#analytics
#analysis
#dataanalysis
#dataanalytics
#excelmvp
#countifs
#filterfunction
#iffunction
#ifformula
#logicaltest
#logicalfunction