Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Explore an in-depth tutorial on using the SUMIFS and SUMIF functions in Excel to add with one or more criteria. Learn through 36 practical examples covering various scenarios, from basic single-condition sums to complex multi-criteria calculations across multiple sheets. Master comparative operators, wildcards, case sensitivity, and handling empty cells. Discover techniques for working with dates, external references, and mismatched data types. Gain insights into efficient formula construction, cell referencing, and potential pitfalls. Apply these skills to enhance your data analysis capabilities and streamline your Excel workflows.

Syllabus

Initial Remarks min mark
Defined Names min mark
Ex 1: Add w 1 condition. min mark
Data Validation List feature min mark
Ex 2: Add w 2 criteria. min mark
Ex3: Add w 3 criteria. min mark
Ex 4: Greater than min mark
Ex 5: Greater than or equal to min mark
Ex 6: Less than min mark
Ex 7: Less than or equal to min mark
Ex 8: Equal min mark
Ex 9: Not min mark
Ex 10: SUMIF if criteria & sum range are same: Saves Typing. min mark
Ex 11: Add w Comparative Operator - Criteria in Cell. min mark
Ex 12: Add w Comparative Operator - Criteria NOT in Cell. min mark
Ex 13: Add w 3 criteria, including "ALL" min mark
Ex 14: Contains Criteria Partial Text Match or Fuzzy Match. min mark
Ex 15: Any Customer with exactly 2 characters. min mark
Ex 16: Criteria typed DIRECTLY into formula often times this is inefficient. min mark
Ex 17: SUMIFS copied down a column. min mark
Ex 18: SUMIFS copied down a column and across rows Mixed Cell References copied to whole table. min mark
Ex 19: Sum Between Two Numbers Lower Included, Upper Not. min mark
Ex 20: Sum Between Two Dates Inclusive. min mark
Ex 21: Sum Cells with Corresponding Empty Cells. min mark
Ex 22: Sum w/ Corr. Empty Cells or Null Text String. min mark
Ex 23: Sum w/ Corr. NOT Empty Cells. Add when there is text, numbers, Null Text String. min mark
Ex 24: Sum w/ Corr. NOT Empty Cells or NOT Null Text String. min mark
Ex 25: Wildcard as Character: Use "~"& to tell SUMIFS to look for * min mark
Ex 26: Criteria for SUMIFS is not case sensitive. min mark
Ex 27: Case Sensitive: Use EXACT inside SUMPRODUCT. min mark
Ex 28: SUMIFS that looks to a different sheet. min mark
Ex 29: SUMIFS Can't Handle 3_D Cell References. min mark
Ex 30: SUMIFS to add from multiple columns across multiple sheets using INDIRECT and SUMPRODUCT functions. min mark
Ex 31: SUMIFS to add from multiple columns across multiple sheets using Sheet Reference and SUMIFS functions on each sheet. min mark
Ex 32: SUMIFS will not read external references when the workbook is closed. min mark
Ex 33: Solutions for SUMIFS with external references: SUMPRODUCT or Formula in External Workbook and Workbook Reference. min mark
Ex 34: SUMIFS with Year Criteria Mismatched against Serial Numbers. min mark 1 hour 57 seconds
Ex 35: SUMIFS with Month As Text Criteria Mismatched against Serial Numbers. min mark 1 hour 3 minutes 48 seconds
Ex 36: SUMIF with only 1 cell in sum_range is volatile. min mark 1 hour, 7 minutes 53 seconds