Lesson 1.4 – Conditional Functions (SUMIF, COUNTIF, AVERAGEIF)
1. What Are Conditional Functions?
Conditional functions perform calculations only when a certain condition (criteria) is met.
They are useful for filtering and calculating data without using filters or pivot tables.
A. SUMIF Function
Purpose: Adds numbers in a range that meet a specific condition.
Syntax:
-
range → The range to check for the condition.
-
criteria → The condition (e.g., “>50”, “Apples”).
-
sum_range (optional) → The range to sum (if different from range).
Example 1 – Add sales above ₹50,000
Example 2 – Add sales for “Apples”
B. COUNTIF Function
Purpose: Counts the number of cells that meet a specific condition.
Syntax:
Example 1 – Count sales above ₹50,000
Example 2 – Count the number of “Apples”
C. AVERAGEIF Function
Purpose: Calculates the average of numbers in a range that meet a condition.
Syntax:
Example – Average sales for “Apples”
D. Key Points & Tips
-
Criteria can be a number, text, or expression (e.g., “>=”, “<“, “<>Banana”).
-
Use cell references for criteria instead of hardcoding. Example:
-
For multiple conditions, you’ll need SUMIFS, COUNTIFS, AVERAGEIFS (covered in next lesson).
Practice Questions – Lesson 1.4
-
Write a formula to sum sales for “Bananas” in the range A2:B15.
-
Count how many orders are less than ₹1,000 in range C2:C20.
-
Calculate the average marks for students whose subject is “Maths” in the range A2:C20.
-
True or False: SUMIF can work with multiple criteria.