Use the COUNTIFS function to count the number of occurrences of a text entry or value within a referenced range, based on multiple criteria, all of which must be met. Use the SUMIFS and AVERAGEIFS functions to tabulate data in a parallel range while using multiple sets of criteria - up to 127 pairs.
- [Voiceover] Reviewing a worksheet called…"multiple criteria."…If you saw the previous movie, you were informed about…the COUNTIF, SUMIF, and AVERAGEIF.…There's a companion set, they all end in the letter "s,"…COUNTIFS, SUMIFS, AVERAGEIFS.…Let's imagine that in cell I2 here,…we want to calculate the number of full time people…who have a job rating of five,…or the number of full time people…who've been here more than eight years,…or maybe both the number of full time people…who've been here more than eight or nine years,…of course we can make that be any number we want,…and have a job rating.…
In other words, we can have multiple criteria here.…A simple COUNTIF would say, for example,…let's see how many people are full time,…but let's include other criteria as well.…Equal COUNTIFS,…left parenthesis, think of the "s" as being plural,…multiple criteria, we're looking in column C, comma,…to see how many of these entries are full time.…If we were to press return here,…we might as well have used COUNTIF.…But, we want multiple criteria,…
Author
Dennis Taylor
Released
5/26/2016- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets
Skill Level Intermediate
Duration
Views
-
Introduction
-
Welcome50s
-
Exercise files25s
-
-
1. Formula and Function Tools
-
2. Formula and Function Tips and Shortcuts
-
3. IF and Related Functions
-
4. Lookup and Reference Functions
-
Use MATCH and INDEX together4m 40s
-
5. Statistical Functions
-
6. Power Functions
-
7. Selected Financial Functions
-
8. Math Functions
-
9. Date and Time Functions
-
10. Text Functions
-
11. Array Formulas and Functions
-
12. Reference Functions
-
13. Information Functions
-
Conclusion
-
Next steps25s
-
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: Tabulate with COUNTIFS, SUMIFS, and AVERAGEIFS