Learn how to use the SUMIFS, AVERAGEIFS and COUNTIFS functions. Author Jen McBee demonstrates how to summarize data using each of these IFS statements.
- [Instructor] There are three functions that…are extremely valuable in Excel 2013.…And those are SUMIFS, AVERAGEIFS, and COUNTIFS.…We can use the SUMIFS function…to find the sum of total sales for the north region…for sales reps with at least five new clients.…Using an IFS statement allows us to put in…more than one set of criteria.…And if both sets of criteria are met,…such as "the north region" and…"at least five new clients,"…then our sales amount will be included in the calculation.…
And if one or more of those are not met,…the amount will be excluded.…So we're going to create a SUMIFS function.…We'll also use AVERAGEIFS and COUNTIFS…so you can see how all three of these work.…Let's go ahead and jump into our…04-03 IFS functions workbook.…Take a look at our information.…We have our sales reps, the region in which they work,…the 2017 sales amount, and how many new clients…they were able to acquire in 2017.…
Looking over in columns F and G,…these are the three questions that we need to answer,…in column F, and we'll put our result in…
Author
Jennifer McBee
Released
5/4/2017Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc. In the managing workbooks chapter, learn how to work with templates, versions, and macros. In the formatting and layout chapter, see how to apply custom formats and styles. The advanced formula chapter covers additional functions including a nested IF. The charts and tables chapter shows how to create a new PivotChart. The course concludes with a sample exam you can use for practice.
- Using templates
- Managing versions
- Leveraging macros
- Linking to external data
- Protecting cells
- Hiding formulas
- Using advanced Fill Series options
- Creating form fields
- Using advanced functions: AND/OR, SUMIFS, AVERAGEIFS, and COUNTIFS
- Manipulating options in existing PivotCharts
Skill Level Advanced
Duration
Views
-
Introduction
-
Welcome57s
-
-
1. Microsoft Office Specialist (MOS) Expert Exam Overview
-
2. Manage and Share Workbooks
-
Create and modify templates4m 15s
-
Link to external data6m 51s
-
-
3. Apply Custom Formats and Layouts
-
Create form fields5m 1s
-
4. Create Advanced Formulas
-
Use a Nested IF function3m 54s
-
Use the AND/OR functions6m 21s
-
Use financial functions3m 24s
-
-
5. Create Advanced Charts and Tables
-
Create new PivotCharts3m 39s
-
Apply styles to PivotCharts4m 20s
-
-
6. Sample Exam
-
Conclusion
-
Next steps30s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Use the SUMIFS, AVERAGEIFS, and COUNTIFS functions