Download Files: http://ift.tt/2xWBRR3
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to create a single cell Array Formula to calculate Total Sales from a Column of Units Sold and a column of Prices (5,000 transactions). See how to use SUMPRODUCT function to calculate the unrounded total, and see how to use the ROUND and SUMPRODUCT function to calculate the rounded total. Learn about the basics of Array Formulas.
Tag: How to
Excel Magic Trick 1461: Ctrl + Enter or Ctrl + D, What is the Difference and When to Use Each?
Excel, Excel 2016, Highline College, Mike Girvin, excelisfun, Excel Magic Trick, Ctrl + D, Ctrl + Enter, Copy Quickly Down Column, Excel keyboard shortcuts, Copy Formula Down Column, Excel Keyboards, Keyboard shortcuts in Excel, What does Ctrl + Enter Do?, What does Ctrl + D do?, Excel Ctrl Enter, Excel Ctrl D, Fill Down, Populate highlighted cells with edited formula
Excel Magic Trick 1460: List All Relative Positions in a Single Cell using TEXTJOIN Array Formula
Download Files: http://ift.tt/2eLv1WF
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn how to look up an item in a list of items and list all the relative positions for that item in a single cell. See the functions TEXTJOIN, IF, ISNA, MATCH. See extensive discussion about Array Formulas. Learn different Formula Element options for listing all relative positions in a formula.
Excel Magic Trick 1459: Adding Food or Accessory Costs For Each Dog with SUMIFS & SUMPRODUCT
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.
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