Ctrl + Shift + Enter - Excel Array Formulas - SUMPRODUCT Function - Including Timing

Overview

Dive into an in-depth 45-minute video tutorial exploring the SUMPRODUCT function in Excel with 21 comprehensive examples. Master array formulas, learn to calculate rounded total sales, handle multiple criteria, and compare efficiency between different methods. Discover how to count transactions by year, work with external references, and optimize formula performance. Gain insights into selecting the most efficient functions for array calculations and understand the nuances of using Ctrl + Shift + Enter. Perfect for Excel enthusiasts looking to enhance their data analysis and problem-solving skills.

Syllabus

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