Ctrl + Shift + Enter: Excel Array Formulas 09: SUMPRODUCT Function: 21 Examples, Including Timing

Описание

Download files here: http://people.highline.edu/mgirvin/excelisfun.htm
EXCEL ARRAY FORMULAS WORK THE SAME IN ANY VERSION OF EXCEL!!!

This video covers:
1. (00:50 min) SUMPRODUCT can be used to house a single array calculation when you need to add the result of the array calculation (Array Operation without Ctrl Shift Enter).
2. (01:23 min) Formula goal: single cell formula to calculate rounded total sales given units column and price per unit column.
3. (02:10 min) Calculation of: =SUMPRODUCT(ROUND(B6:B10*C6:C10,2))
4. (03:53 min) Multiply arrays with same dimensions and then add.
5. (04:53 min) SUMPRODUCT treats array entries that are not numeric as if they were zeros.
6. (06:04 min) If you are multiplying arrays that are not the same dimension, you can use the multiplication operator *, but watch out for text entries.
7. (08:19 min) Excel 2003 and earlier, SUMPRODUCT great for counting and adding with more than one criteria.
8. (08:19 min) The array argument in the SUMPRODUCT function is programmed to perform array calculations without the keystroke Ctrl + Shift + Enter.
9. (13:26 min) Calculation of =SUMPRODUCT(--(B62:B68=F62),--(C62:C68=G62),D62:D68).
(15:09 min) Timing Different Formulas with Charles Williams VBA timing code.
10. (19:08 min) Don't overuse the SUMPRODUCT function, when other functions may be more efficient (may require timing)..
11. (19:08 min)Formula goal: Count transactions in year 2012 given a column of serial number dates (criterion mismatch problem). See four methods.
12. (19:36 min) Method 1: COUNTIF and YEAR function helper column.
13. (21:12 min) Method 2: single cell non-Array Formula using COUNTIFS.
14. (23:23 min) Method 3: single cell Array Formula using SUMPRODUCT, YEAR and Double Negative.
15. (24:24 min) Method 4: single cell Array Formula using SUM, YEAR and IF function.
(25:15 min) Timing 4 methods.
16. (26:34 min) SUMPRODUCT can handle external references when external workbook is closed, but COUNTIFS cannot: 4 examples.
17. (29:16 min) 8 Methods for counting years from serial dates
18. (29:38 min) If you use SUMPRODUCT to house array calculation, pick function for array calculation carefully: may require timing.
19. (29:38 min) Formula to count with year & month criteria that is mismatched against serial number data (3 examples).
(40:45 min) Timing for 3 examples.
20. (41:45 min) Selecting most efficient functions for Array Formula can reduced calculation time (may require timing).
21. (42:22 min) IF Function's requirement to use Ctrl + Shift + Enter trumps other functions requirement to NOT use Ctrl + Shift + Enter, ALWAYS.
THIS VIDEO SERIES AT YOUTUBE IS THE SAME AS THE DVD FROM EXCELISFUN. THESE VIDEOS ARE BEING GIVEN AWAY FOR FREE AT YOUTUBE. SUPPORT THE CAUSE BY GOING TO AMAZON AND BUYING THE BOOK.
EXCEL ARRAY FORMULAS WORK THE SAME IN ANY VERSION OF EXCEL!!!
Buy Ctrl + Shift + Enter: Mastering Excel Array Formulas DVD at A: http://www.amazon.com/Ctrl-shift-enter-Mastering-Formulas/dp/1615470085
Ctrl + Shift + Enter: Mastering Excel Array Formulas Book: http://www.amazon.com/Ctrl-Shift-Enter-Efficient-Calculating/dp/1615470077
Ctrl+Shift+Enter: A Book About Building Efficient Formulas, Advanced Formulas, and Array Formulas for Data Analysis and Calculating Problems
Designed with Excel gurus in mind, this handbook outlines how to create formulas that can be used to solve everyday problems with a series of data values that standard Excel formulas cannot or would be too arduous to attempt. Beginning with an introduction to array formulas, this manual examines topics such as how they differ from ordinary formulas, the benefits and drawbacks of their use, functions that can and cannot handle array calculations, and array constants and functions. Among the practical applications surveyed include how to extract data from tables and unique lists, how to get results that match any criteria, and how to utilize various methods for unique counts. This book contains 529 screen shots.
Full playlist of videos at YouTube: http://www.youtube.com/playlist?list=PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci