Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

YouTube

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

ExcelIsFun via YouTube

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..

Taught by

ExcelIsFun

Reviews

Start your review of Ctrl + Shift + Enter - Excel Array Formulas - SUMPRODUCT Function - Including Timing

Never Stop Learning.

Get personalized course recommendations, track subjects and courses with reminders, and more.

Someone learning on their laptop while sitting on the floor.