Excel Array Formulas and the SUMPRODUCT Function
Webinar Details $219
- Rated:
- Webinar Length: 80 Minutes
- Guest Speaker: Michael Souder
- Topic: Business Administration, Finance, Human Resources, Taxation and Accounting
- Credit: ATAAA 1.0, ATATX 1.0, ATAOP 1.0, CPE 2.0
-
Array formulas and the SUMPRODUCT function are two of the most powerful tools that allow the Excel user to perform many complex computations with simplicity. While these tools have many applications, most users have never heard of them or have been intimidated by the learning curve to become proficient in their use. This class demystifies the concepts behind these tools and uses several real-world examples incorporating common functions to unlock their power and versatility.
This class includes the following:
- Explains arrays.
- Distinguishes single cell and multi cell arrays.
- Demonstrates how to use array constants.
- Builds simple array formulas with the SUM function.
- Introduces Booleans in array formulas.
- Builds more complex array formulas using the IF, SUM and AVERAGE functions.
- Introduces additional array formula possibilities using the LEN, LARGE and SMALL functions.
- Explains the SUMPRODUCT function.
- Solves complex calculations using the SUMPRODUCT function.
This engaging and interactive class is a must attend for intermediate to advanced Excel users looking to expand their skill set. Join Michael Souder as he helps you unlock the power of array formulas and the SUMPRODUCT function and shows you how to apply them in the real world.
- Introduction
- Topics 00:02:09
- What is an Array? 00:03:32
- Simple Array Formula 00:04:44
- Array Basics 00:09:04
- Single Cell and Multiple Cell Arrays 00:15:24
- Multiple Cell Arrays Rules 00:16;34
- How To Use Array Constants 00:26:08
- Simple Array Formulas SUM, MIN, and MAX Functions 00:35:07
- Simple Array Formulas - LEN Function 00:38:46
- Complex Array Formulas - Booleans 00:42:43
- Complex Array Formulas - LARGE and SMALL 01:00:30
- Complex Array Formulas -SUMIF, MATCH, and INDEX 01:06:30
- SUMPRODUCT Function 01:18:18
- SUMPRODUCT Function - Example 01:18:48
- SUMPRODUCT Function (cont’d) - Example 01:22:32
- Appendix - Functions Inventory 01:32:42
- Attendee Questions 01:34:02
- Presentation Closing 01:40:10
- Array Constants 00:26:08
- Array Formula 00:02:16, 00:03:34, 00:04:46
- Booleans 00:42:49, 01:22:36
- IF Function 00:43:19
- INDEX 01:07:38
- LARGE Function 01:00:45
- LEN 00:38:48
- MATCH 01:07:05
- MAX 00:36:13
- MIN 00:35:45
- SMALL Function 01:00:45
- SUM 00:05:23, 00:14:04, 0:35:11
- SUMIF 01:06:36
- SUMPRODUCT 00:02:44
Array Constants: Array constants are often used in array formulas to create or manipulate several values at once, rather than a single value. There are three types: Horizontal, Vertical, and Two dimensional.
Array Formula: In Excel, an Array Formula allows you to do powerful calculations on one or more value sets. The result may fit in a single cell or it may be an array. An array is just a list or range of values, but an Array Formula is a special type of formula that must be entered by pressing Ctrl+Shift+Enter.
Booleans: A boolean is a data type with only two possible values, TRUE or FALSE. You'll often see boolean results or boolean expressions in Excel.
IF Function: Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
INDEX Function: The INDEX function can be used to return data from within a given range based on a row and/or column number that you specify.
LARGE Function: The =LARGE function can be used to return the largest, second largest, third largest and kth largest values from a range.
LEN Function: The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.
MATCH Function: The MATCH function searches a prescribed range for specified criteria and returns a column or row number if a match is found. MATCH can be used with other functions that require a column or row number.
MAX Function: The Microsoft Excel MAX function returns the largest value from the numbers provided. The MAX function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the MAX function can be entered as part of a formula in a cell of a worksheet.
MIN Function: The Microsoft Excel MIN function returns the smallest value from the numbers provided. The MIN function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel.
SMALL Function: Use this function to return values with a particular relative standing in a data set.
SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet
SUMIF: A look-up function in Excel that allows you to add up numbers based upon a criterion that you specify. Unlike VLOOKUP, the SUMIF function can add up two or more values and returns zero (instead of #N/A) if no match is found.
SUMPRODUCT: The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.
