Download Files: http://ift.tt/2xNrzlB
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn how to create use SUMIFS and SUMPRODUCT functions to add the costs for each dog based on whether the expense is for Food or it is an Accessory. This video shows how to use SUMIFS to add with with one condition or another condition, using a function argument array operation to run an OR Logical Test for a sum total.
Month: August 2017
Excel Magic Trick 1457 Part 2: Regional Settings & Text or Number Date / Times in SUMIFS Function
Download File: http://ift.tt/2v5xN3c
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
In the English United States Regional Settings, SUMIFS seems to be recognizing Text and Number Dates as equivalent Criteria. But when we change the Reginal Settings, SUMIFS correctly interprets Text Dates and Number Dates as different. This Tip comes from Awesome Online Teammate, Bill Szysz!!!
Lookup Price in Multiple Tables: VLOOKUP, INDIRECT & Defined Names – Excel Magic Trick 1458
Download Files:
Start File: http://ift.tt/2wjdQDn
Finished File: http://ift.tt/2wDywsd
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn how to lookup the price for PVC Pipe in three different tables using VLOOKUP and INDIRECT Function and Defines Names. Defined Names allow us to assign a Named Reference to a lookup table. The INDIRECT Function allows us to use the table name entered as text in each transaction to access the Defined Name Price Lookup Table. VLOOKUP allows us lookup the price based on the Pipe Size entered for each transaction.
Excel Magic Trick 1457: Text or Number Date / Times in Functions & Direct Operations (10 Examples)
Download File: http://ift.tt/2v5xN3c
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how different functions in Excel and Direct Operations interact and interpret Date and Time Values with the different Data Types: Number or Text. Learn how the Functions: SUMIFS, COUNTIFS, COUNT, SUM, EOMONTH, MROUND, DATE and SUMPRODUCT treat Text Dates & Number Dates and Text Times & Number Times. Learn that Direct Operations treat Text Dates and Numbers dates differently and that many Excel Functions treat Text and Number Dates/Times as equivalent.
Excel Magic Trick 1456: PivotTable & Slicer to Create 8 Year Sales Report by Product & Month
In this video see how to create an 8 Year Span Monthly Revenue Report for a selected Product. Learn how to take
Download File: http://ift.tt/2x2okpW
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Excel Magic Trick 1455: Convert Text Month Criteria to Upper & Lower Dates for SUMIFS Function
In this video see how to create an 8 Year Span Monthly Revenue Report for a selected Product. Learn how to take Text Month Criteria like “Jan” 2010 and convert it to the proper dates 1/1/2010 and 1/31/2010 to use as upper and lower dates for adding sales for each month.
Download File: http://ift.tt/2x2okpW
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Excel Magic Trick 1454 DAX Measure Count Customer Totals Between Upper Lower Limits, Each Month
Count Customer Totals Between Upper & Lower Limits For Each Month using a DAX Measure in either Power Pivot or Power BI Desktop. See the DAX Functions: COUNTROWS, FILTER, MAX and MIN and an AND Logical Test. Create a Cross Tabulated Frequency Distribution that shows a count of Customer Totals for each month and each Sales Category.
Download File:
Start File: http://ift.tt/2vu0rIq
Finished File: http://ift.tt/2vLpetY
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Related Videos:
Excel Magic Trick 1453 Array Formula Count Customer Totals Between Upper Lower Limits, Each Month
Excel Magic Trick 1454 DAX Measure Count Customer Totals Between Upper Lower Limits, Each Month
Excel Magic Trick 1453 Array Formula Count Customer Totals Between Upper Lower Limits, Each Month
Count Customer Totals Between Upper & Lower Limits For Each Month using an Excel Array Formula and the functions SUMPRODUCT and SUMIFS. Create a Cross Tabulated Frequency Distribution that shows a count of Customer Totals for each month and each Sales Category.
Download File: http://ift.tt/2vP7LBc
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Related Videos:
Excel Magic Trick 1453 Array Formula Count Customer Totals Between Upper Lower Limits, Each Month
Excel Magic Trick 1454 DAX Measure Count Customer Totals Between Upper Lower Limits, Each Month
Excel Magic Trick 1452: Max Monthly Customer Total for Year: Array Formula or PivotTable?
See how to calculate the single largest Customer Monthly Total from 81 customers over a 12-month period.
Download File:
Start File: http://ift.tt/2vEf7HN
Finished File: http://ift.tt/2wxchAS
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
See two methods; 1) PivotTable Method, 2) Array Formula Method. The PivotTable Method is the easy way to accomplish this. The Array Formula method is a difficult Array Formula, but it can make all calculations in a single cell and will update instantly if source data changes. For this array formula you will learn some important concepts for Function Argument Array Operations and how to create a rectangular resultant array by carefully creating the Array Constants and Resultant Arrays with the correct Array Syntax. Learn that Curly Brackets house the array, Semi-colons represent Rows and Commas represent Columns. See the Excel Spreadsheet Functions: AGGREGATE, SUMIFS and EDATE.
Topics in This Video:
1. (00:04) File for download
2. (00:13) Introduction
3. (00:56) PivotTable Solution
4. (02:51) Array Formula Solution
5. (16:26) Summary
Excel Magic Trick 1451: Hack Data Model, Date Table & DAX Measures WITHOUT buying Power Pivot
Learn about DAX Functions: CALCULATE, SAMPERIODLASTYEAR, TOTALYTD, DATEADD, IF, AND, HASONEVALUE and More!
Learn How to Define a Variable in a DAX Measure using VAR and RETURN Keywords
Learn how to build a complete Data Model and DAX Formulas without Power Pivot in Excel 2016
Download File:
Start File: http://ift.tt/2unJw83
Finished File: http://ift.tt/2u7PyOO
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Topics in This Video:
1. (00:04) How to Download Files.
2. (00:14) Introduction to building Data Model with Relationships and DAX Formulas without buying Power Pivot in Excel 2016.
3. (02:47) Import Tables and Build Relationships using the Relationship button in the Data Ribbon Tab.
4. (04:45) Build Data Table in Excel, including TEXT Function formula to get Months to Sort Correctly in a Data Model PivotTable, without using the Sort By Column feature. See the Fill Series Trick to get a complete list of dates, see the functions YEAR, MONTH and TEXT.
5. (09:23) Add Relationship to Date Table.
6. (09:43) Start building Data Model PivotTable and notice a problem with month names not sorting correctly. Then fix it with the TEXT Function using custom Number Formatting “mm – mmm”.
7. (12:04) Create first DAX Measure (Formula) to add Total Sales. See SUM Function and how to add Number Formatting to Measure.
8. (13:42) What NOT to do when you are hacking the Data Model. Do not drag Sales or Date Fields to Values Area or Row Area of Pivot Table. These are called Implicit Measures and they are not efficient.
9. (14:47) Discuss Formula to get Last Year’s Sales.
10. (15:04) Discussion about Filter Context and how DAX Measures calculate or evaluate to get the correct answer.
11. (16:28) Create Second Measure (Formula) to get Last Year’s Sales using the CALCULATE and SAMEPERIODLASTYEAR DAX Functions.
12. (19:22) How to hide Grand Total Formula with IF and HASONEVALUE DAX Functions. We use HASONEVALUE Function on the Year Column.
13. (22:27) Formula to calculate Difference From Last Year using Two Previously Defined Measures and the IF Function.
14. (24:00) Third DAX Measure (Formula) for Running Total using TOTALYTD, IF and HASONEVALUE function on the Month Column.
15. (27:28) Fourth DAX Measure (Formula) for Difference from Last Month using CALCULATE, DATEADD, IF, HASONEVALUE, and AND DAX Functions. Also see Variable in DAX.
16. (29:50) Create Variable in Excel 2016 DAX Function language using the VAR and RETURN Keywords.
17. (34:26) Summary
Excel Power Pivot, How to Get Power Pivot For Free, How Do I Get Power Pivot?, Build Complete Data Model without Power Pivot, DAX Formulas in Excel, Excel 2016, Highline College, Mike Girvin, excelisfun, Excel Magic Trick, Data Model PivotTables, Excel 2016 Relationships, Relationships in Excel, Multiple Tables in PivotTable Field List, CALCULATE, SAMPERIODLASTYEAR, TOTALYTD, DATEADD, IF, AND, HASONEVALUE, DAX Measures, DAX Formulas, How to Create Variable in DAX in Excel 2016, VAR in Excel, DAX Variable in Excel