SUMPRODUCT Demo

Microsoft Excel: Advanced Formulas & Functions

Getting Started

Excel Formulas 101

Introduction (1:48)
Formula Syntax (11:03)
Fixed & Relative References (8:42)
Common Error Types (8:47)
Formula Auditing: Trace Precedents & Dependents (10:15)
Formula Auditing: Evaluate Formula & Error Checking (7:48)
Ctrl Shortcuts (7:30)
Function Shortcuts (9:44)
Alt Key Tips (4:17)
BONUS: Mac Shortcuts
PRO TIP: Data Validation (6:12)
Congrats, You're a Developer! (5:07)
QUIZ: Formulas 101
HOMEWORK: Formulas 101 (0:46)

Conditional Statements & Logical Operators

Introduction (1:23)
Anatomy of the IF Statement (4:01)
Nested IF Statements (4:53)
AND/OR Operators (8:47)
NOT (<>) Operators (3:17)
Addressing Errors with IFERROR (4:03)
Common IS Statements (4:00)
QUIZ: Conditional Statements & Logical Operators
HOMEWORK: Conditional Statements & Logical Operators (0:44)

Statistical Functions

Introduction (1:09)
Basic Stats Functions (5:17)
SMALL/LARGE & RANK/PERCENTRANK (6:15)
RAND() & RANDBETWEEN (2:12)
The SUMPRODUCT Function (3:55)
SUMPRODUCT Demo (4:57)
COUNTIFS/SUMIFS/AVERAGEIFS (4:49)
DEMO: Basic Dashboards with Stats Functions (8:09)
Combining COUNTIF & SUMPRODUCT (7:57)
PRO TIP: Data Profiling (16:06)
QUIZ: Statistical Functions
HOMEWORK: Statistical Functions (0:35)

Lookup & Reference Functions

Introduction (1:18)
Named Arrays (3:30)
ROW/ROWS & COLUMN/COLUMNS (3:18)
VLOOKUP & HLOOKUP (5:53)
DEMO: Joining Data with VLOOKUP (6:23)
Combining IFERROR & VLOOKUP (4:30)
VLOOKUP Range Options (6:18)
Approximate Match Lookups (5:05)
The INDEX Function (1:59)
The MATCH Function (2:32)
Combining INDEX & MATCH (6:08)
Combining MATCH & VLOOKUP (4:47)
UPDATE: VLOOKUP Correction (5:05)
SNEAK PEEK: XLOOKUP (Office 365 Only) (4:29)
DEMO: Building Dashboards with XLOOKUP (18:16)
Selecting List Items with CHOOSE (13:15)
The OFFSET Function (2:05)
Combining OFFSET & COUNTA (2:58)
DEMO: Building a Scrolling Chart with OFFSET (9:51)
QUIZ: Lookup & Reference Functions
HOMEWORK: Lookup & Reference Functions (0:54)

Text Functions

Introduction (0:50)
UPPER, LOWER, PROPER & TRIM (3:32)
CONCATENATE (&) (3:26)
LEFT, MID, RIGHT & LEN (3:31)
TEXT & VALUE (4:20)
SEARCH & FIND (5:04)
Categorizing Data with IF(ISNUMBER(SEARCH)) (5:39)
Combining RIGHT, LEN & SEARCH (5:43)
The SUBSTITUTE Function (2:21)
QUIZ: Text Functions
HOMEWORK: Text Functions (0:59)

Date & Time Functions

Introduction (2:08)
Understanding DATEVALUE (5:05)
Date Formatting & Fill Series (3:39)
TODAY() & NOW() (2:16)
YEAR/MONTH/DAY & HOUR/MINUTE/SECOND (3:04)
The EOMONTH Function (6:17)
The YEARFRAC Function (2:27)
WEEKDAY, WORKDAY & NETWORKDAYS (7:22)
The DATEDIF Function (4:23)
DEMO: Budget Pacing Tool (11:07)
QUIZ: Date & Time Functions
HOMEWORK: Date & Time Functions (0:35)

Formula-Based Formatting

Introduction (1:26)
Creating and Managing Formula-Based Rules (4:39)
DEMO: Highlighting Rows with MOD (3:50)
DEMO: Formatting Cells Based on Values (3:29)
DEMO: Formatting Cells with Stats Functions (5:28)
DEMO: Formatting Cells with Text & Conditional Functions (5:08)
QUIZ: Formula-Based Formatting
HOMEWORK: Formula-Based Formatting (0:34)

Basic Array Formulas

Introduction (1:24)
READ ME: Array Formulas are Changing!
Rules of Array Formulas (3:24)
Pros & Cons of Array Formulas (2:15)
Vertical, Horizontal & 2-D Array Constants (6:26)
Using Array Constants in Formulas (3:38)
Named Array Constants (4:31)
The TRANSPOSE Function (4:21)
Linking Data: Array vs. Non-Array Comparison (2:36)
DEMO: Returning the "X" Largest Values (3:09)
DEMO: Counting Characters Across Cells (1:52)
DEMO: Creating a "MAX IF" Function (Part 1) (3:09)
DEMO: Creating a "MAX IF" Function (Part 2) (6:54)
The Double Unary Operator ("--") (3:49)
QUIZ: Array Formulas
HOMEWORK: Array Formulas (2:09)

Extra Bonus Functions

Introduction (1:38)
The INDIRECT Function (10:55)
The HYPERLINK Function (6:19)
WEBSERVICE & FILTERXML: Weather Underground (7:16)
WEBSERVICE & FILTERXML: Open Weather Map (9:14)
QUIZ: Bonus Functions