Completed
min) If you use SUMPRODUCT to house array calculation, pick function for array calculation carefully: may require timing.
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Ctrl + Shift + Enter - Excel Array Formulas - SUMPRODUCT Function - Including Timing
Automatically move to the next video in the Classroom when playback concludes
- 1 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 min) Formula goal: single cell formula to calculate rounded total sales given units column and price per unit column.
- 3 min) Calculation of: =SUMPRODUCT(ROUND(B6:B10*C6:C10,2))
- 4 min) Multiply arrays with same dimensions and then add.
- 5 min) SUMPRODUCT treats array entries that are not numeric as if they were zeros.
- 6 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 min) Excel 2003 and earlier, SUMPRODUCT great for counting and adding with more than one criteria.
- 8 min) The array argument in the SUMPRODUCT function is programmed to perform array calculations without the keystroke Ctrl + Shift + Enter.
- 9 min) Calculation of =SUMPRODUCT(--(B62:B68=F62),--(C62:C68=G62),D62:D68).
- 10 min) Timing Different Formulas with Charles Williams VBA timing code.
- 11 min) Don't overuse the SUMPRODUCT function, when other functions may be more efficient (may require timing)..
- 12 min)Formula goal: Count transactions in year 2012 given a column of serial number dates (criterion mismatch problem). See four methods.
- 13 min) Method 1: COUNTIF and YEAR function helper column.
- 14 min) Method 2: single cell non-Array Formula using COUNTIFS.
- 15 min) Method 3: single cell Array Formula using SUMPRODUCT, YEAR and Double Negative.
- 16 min) Method 4: single cell Array Formula using SUM, YEAR and IF function.
- 17 min) Timing 4 methods.
- 18 min) SUMPRODUCT can handle external references when external workbook is closed, but COUNTIFS cannot: 4 examples.
- 19 min) 8 Methods for counting years from serial dates
- 20 min) If you use SUMPRODUCT to house array calculation, pick function for array calculation carefully: may require timing.
- 21 min) Formula to count with year & month criteria that is mismatched against serial number data (3 examples).
- 22 min) Timing for 3 examples.
- 23 min) Selecting most efficient functions for Array Formula can reduced calculation time (may require timing).
- 24 min) IF Function's requirement to use Ctrl + Shift + Enter trumps other functions requirement to NOT use Ctrl + Shift + Enter, ALWAYS.
