Completed
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).
Class Central Classrooms beta
YouTube playlists 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.
