Download Excel File:
http://people.highline.edu/mgirvin/ExcelIsFun
.htm
Learn how to add with one or more criteria (conditions) using SUMIFS and SUMIF functions. Topics in this video can be equally applied to the COUNTIF, COUNTIFS, AVERAGEIF and AVERAGEIFS functions.
Topics:
Initial Remarks (min mark 00:01 )
Defined Names (min mark 2:40 )
Ex 1: Add w 1 condition. (min mark 4:07 )
Data Validation List feature (min mark 6:42 )
Ex 2: Add w 2 criteria. (min mark 8:40 )
Ex3: Add w 3 criteria. (min mark 10:34 )
Comparative Operators:
Ex 4:
Greater than (min mark 11:49 )
Ex 5:
Greater than or equal to (min mark 11:49 )
Ex 6:
Less than (min mark 11:49 )
Ex 7:
Less than or equal to (min mark 11:49 )
Ex 8:
Equal (min mark 11:49 )
Ex 9: Not (min mark 11:49 )
Ex 10: SUMIF if criteria & sum range are same:
Saves Typing. (min mark 15:37 )
Ex 11: Add w Comparative
Operator -
Criteria in
Cell. (min mark 17:05 )
Ex 12: Add w Comparative Operator - Criteria NOT in Cell. (min mark 18:34 )
Ex 13: Add w 3 criteria, including "ALL" (min mark 22:19 )
Ex 14: Contains Criteria (Partial
Text Match or
Fuzzy Match). (min mark 23:45 )
Ex 15: Any
Customer with exactly 2 characters. (min mark 24:46 )
Ex 16: Criteria typed DIRECTLY into formula (often times this is inefficient). (min mark 26:39 )
Ex 17: SUMIFS copied down a column. (min mark 27:26 )
Ex 18: SUMIFS copied down a column and across rows
Mixed Cell References (copied to whole table). (min mark 28:40 )
Ex 19: Sum Between Two
Numbers (Lower Included, Upper Not). (min mark 32:28 )
Ex 20: Sum Between Two
Dates Inclusive. (min mark 34:19 )
Ex 21: Sum Cells with Corresponding
Empty Cells. (min mark 35:37 )
Ex 22: Sum w/ Corr. Empty Cells or
Null Text
String. (min mark 35:37 )
Ex 23: Sum w/ Corr. NOT Empty Cells. (Add when there is text, numbers, Null Text String). (min mark 35:37 )
Ex 24: Sum w/ Corr. NOT Empty Cells or NOT Null Text String. (min mark 35:37 )
Ex 25:
Wildcard as
Character: Use "~"& to tell SUMIFS to look for * (min mark 39:48 )
Ex 26: Criteria for SUMIFS is not case sensitive. (min mark 41:31 )
Ex 27:
Case Sensitive: Use EXACT inside SUMPRODUCT. (min mark 42:08 )
Ex 28: SUMIFS that looks to a different sheet. (min mark 46:25 )
Ex 29: SUMIFS Can't Handle 3_D Cell References. (min mark 48:17 )
Ex 30: SUMIFS to add from multiple columns across multiple sheets using INDIRECT and SUMPRODUCT functions. (min mark 51:40 )
Ex 31: SUMIFS to add from multiple columns across multiple sheets using Sheet
Reference and SUMIFS functions on each sheet. (min mark 49:59 )
Ex 32: SUMIFS will not read external references when the workbook is closed. (min mark 56:45 )
Ex 33: Solutions for SUMIFS with external references: SUMPRODUCT or
Formula in External
Workbook and Workbook Reference. (min mark 58:37 )
Ex 34: SUMIFS with Year Criteria Mismatched against
Serial Numbers. (min mark 1:00:57 1 hour 57 seconds)
Ex 35: SUMIFS with Month As Text Criteria Mismatched against Serial Numbers. (min mark 1:03:48 1 hour 3 minutes 48 seconds )
Ex 36: SUMIF with only 1 cell in sum_range is volatile. (min mark 1:07:53 1 hour, 7 minutes 53 seconds)
Add with 1 one condition, Add with 1 one criterion, Add with 1 one criteria, Add with 2 two condition, Add with 2 two criteria, Add with
3 three condition, Add with 3 three criteria, Add with 1 one or more condition, Add with 1 one or more criteria multiple criteria conditions
- published: 29 Nov 2012
- views: 183467