Hi! We show you're using Internet Explorer 6. Unfortunately, IE6 is an older browser and everything at MindBites may not work for you. We recommend upgrading (for free) to the latest version of Internet Explorer from Microsoft or Firefox from Mozilla.
Click here to read more about IE6 and why it makes sense to upgrade.

Series: Microsoft Excel: IF, Nested IF, SUMIF & COUNTIF

Preview

Buy the Series and be able to watch all of the lessons.

About this Series

  • Lessons: 4
  • Total Time: 0h 54m
  • Use: Watch Online
  • Access Period: 360 Days
  • Created At: 07/14/2011
  • Last Updated At: 07/14/2011

This series of four videos covers the nuts and bolts of IF statements, nested IF statements, and the conditional formulas (SUMIF and COUNTIF) in Microsoft Excel. This series is designed for students and coaches seeking to learn access to compete in the UIL High School Computer Applications contest but has proven to be useful for anyone seeking to learn or brush up on their Excel skills.

In the first video, we focus on the IF formula and construction of IF statements in Excel. In this video, you'll learn:
- how the IF formula works
- how to think of the IF formula as a sentence in order to help with constructing accurate IF statements
- how to diagram an IF statement graphically, which will help you to conceptualize and understand how nested IF statements work, which we'll cover in latter videos
- what the parts are to an IF statement and how to think about these types of formulae and statements

In the second video lesson, we introduce you to a nested IF statement in Excel. A nested IF statement is a formula that includes more than one IF statement within it. In this video, you'll learn:
- how to think about construction of IF statements and nested IF statements
- how to diagram IF statements and nested IF statements graphically, which will help you to master the creation of complex IF statements
- what the parts are to an IF statement and how to think about nesting multiple IF statements in situations where you have multiple decision criteria that need to be considered in order to output the correct value.

In the third video, we dig deeper on the subject of nested IF statements by walking through an example which requires the use of 5 disparate IF statements nested into one formula. A nested IF statement is a formula that includes more than one IF statement within it. In this video, you'll learn:
- how to think about construction of IF statements and nested IF statements
- tips and tricks to use to know how to sequence different decision criteria in your nested IF statement
- how multiple different approaches to the same nested IF statement can yield equally viable results despite differences in approach. This will also give you some insight into some personal preferences you may find that you have when it comes to setting up nested IF statements.
- how to reality check your if statement by walking through some examples to see how sample values are treated by the nested IF statement

In the final lesson, we will focus our attention on the COUNTIF and SUMIF formulas in Excel, which allow you to calculate conditional counts and conditional sums. In this video, you'll learn:
- how COUNTIF and SUMIF work and when they're applicable
- the input variables for both COUNTIF and SUMIF formulas (including an explanation of when the 'sum range' input variable for a SUMIF formula is an optional input)
- how to count the number of cells within a range that match a criteria than you stipulate (using the COUNTIF function)
- how to sum up the values in a range of cells that meet a criteria you stipulate (using the SUMIF formula)
- how to use the SUMIF formula in such a way that you values you sum are related to but not the same as the values you consider when evaluating your indicated criteria.
- examples of situations when you may find the SUMIF and COUNTIF formulae to be useful
- tips and tricks as well as suggestions of what to watch out for when using both COUNTIF and SUMIF

To illustrate these things, we'll go through some examples in Microsoft Excel. Note that a starting point worksheet is attached as is the ending point worksheets (in the attached Excel workbook) for each video in order to allow you to work off of the starting document and compare your results to the ending document.

These videos are screencasted. To watch them in the resolution in which they were recorded (which will offer a crisper screen resolution than the player here), move your mouse over the player and click the 100% (Actual Size) button.

These videos were recorded using Microsoft Excel 2010. There are very minor differences between Excel 2007 and 2010, but the content covered here is virtually the same across versions.

About this Author

Hexco Academic
Hexco Academic
30 lessons
Joined:
02/21/2011

Academic publishing company for 28+ years offering instructional videos, books, software, virtual flashcards, workshops, spelling coaching, and geographic coaching. Most products are geared for competitive academics and home schooling to include materials to study for the Scripps' National Spelling Bee, National Geographic Bee, Texas University Interscholastic League (UIL), North South Foundation, TAPPS, SAT, and more. The 2010 National Spelling Bee champion, Anamika Veeramani was the 11th champion in a row to use our products. Studio Hexco at MindBites is a collection of course material and live presentations by experts in a variety of popular topics. We welcome and appreciate your...

Lessons Included

None of the lesson in this series have been reviewed.

Below are the descriptions for each of the lessons included in the series:

Supplementary Files: