Excel Advanced Working with Functions
ADVANCED Working with Functions
Use COUNTIF AVERAGEIF and SUMIF to calculate results from worksheet data that meets specified criteria.
Working with Functions Overview
This topic focuses on three criteria based functions from the course source: COUNTIF and AVERAGEIF as statistical functions and SUMIF as a mathematical function.
Each function evaluates a specified criterion and then counts averages or sums the matching worksheet data.
Essential Terms
Review the vocabulary used when applying criteria based functions.
Statistical Functions
COUNTIF and AVERAGEIF apply a criterion to worksheet data and return different statistical results.
Compare the Functions
Choose the function according to the result required from matching data.
COUNTIF
Counts cells in a range that meet a specified criterion.
AVERAGEIF
Calculates an average for values associated with a specified criterion.
SUMIF
Adds values associated with a specified criterion.
Criteria Based Workflow
Start with the required result then identify the range and criterion needed by the function.
SUMIF Mathematical Function
SUMIF performs conditional addition and can use a separate range for the values to be summed.
Interactive Function Explorer
Explore how COUNTIF AVERAGEIF and SUMIF answer different questions from criteria based data.
Key Takeaways
Match the required calculation to the correct criteria based function.
Use COUNTIF to count matching cells AVERAGEIF to average matching data and SUMIF to total matching data.
The criterion determines which data is included in the calculation so confirm that it expresses the condition you intend to test.
Knowledge Check
Test your understanding of COUNTIF AVERAGEIF and SUMIF.