AVERAGEIF and AVERAGEIFS in Excel - Step-by-Step Guide

Introduction

AVERAGEIF and AVERAGEIFS help you find the average of numbers that meet one or more conditions. This is useful when you want to calculate averages only for specific groups or criteria in your data.

When you want to find the average sales for a specific product only.

When you need the average score of students who passed an exam.

When calculating the average expense for a certain month.

When averaging values that meet multiple conditions, like sales in a region and above a certain amount.

When you want to ignore data that does not meet your criteria to get a more accurate average.

Steps

Step 1: Click

- cell where you want the average result

The cell is selected and ready for formula input

Step 2: Type

- the selected cell

Formula bar shows the formula you type

💡 Start typing =AVERAGEIF( or =AVERAGEIFS( to use the functions

Step 3: Enter

- the range of cells to check the condition (criteria_range)

Formula bar shows the range inside the parentheses

Step 4: Enter

- the condition to apply (criteria), like ">50" or "Apples"

Formula bar shows the condition after the range

Step 5: For AVERAGEIF, optionally enter

- the range of cells to average (average_range)

Formula bar shows the average range if different from criteria range

Step 6: For AVERAGEIFS, enter

- pairs of criteria ranges with their conditions

Formula bar shows all ranges and conditions in pairs

Step 7: Press Enter

- formula bar

Cell shows the average of values that meet the condition(s)

Before vs After

Before

A list of sales amounts for different products in column B and product names in column A

After

A cell shows the average sales amount only for the product named "Apples" using =AVERAGEIF(A2:A10, "Apples", B2:B10)

Settings Reference

criteria_range

📍 first argument in AVERAGEIF or first argument in AVERAGEIFS

The cells checked against the condition

Default: none

criteria

📍 second argument in AVERAGEIF or second, fourth, sixth, etc. arguments in AVERAGEIFS

The condition that cells must meet to be included

Default: none

average_range

📍 optional third argument in AVERAGEIF

The cells to average if different from criteria_range

Default: criteria_range

Common Mistakes

Using AVERAGEIF with multiple conditions separated by commas

AVERAGEIF only supports one condition; multiple conditions require AVERAGEIFS

Use AVERAGEIFS to apply multiple conditions, listing each criteria_range and criteria pair

Not using quotes around text or expressions in criteria

Excel treats unquoted text or expressions as cell references or errors

Always put text or expressions like ">50" inside double quotes

Using different sized ranges for criteria_range and average_range

Ranges must be the same size or Excel returns an error

Make sure criteria_range and average_range cover the same number of rows and columns

Summary

AVERAGEIF calculates the average of numbers that meet one condition.

AVERAGEIFS calculates the average of numbers that meet multiple conditions.

Always use quotes for text or comparison operators in criteria.

Ranges for criteria and average must be the same size.

Practice

1. What does the AVERAGEIF function do in Excel?

easy

A. Finds the maximum value in a range.

B. Calculates the sum of all cells in a range.

C. Counts the number of cells that meet multiple conditions.

D. Calculates the average of cells that meet one specific condition.

Solution

1. Step 1: Understand the purpose of AVERAGEIF

   AVERAGEIF calculates the average of cells that satisfy a single condition.

2. Step 2: Compare with other functions

   Unlike SUM or COUNT, it focuses on averaging with one condition.

3. Final Answer:

   Calculates the average of cells that meet one specific condition. -> Option D

4. Quick Check:

   AVERAGEIF = average with one condition [OK]

Hint: AVERAGEIF averages cells matching one condition only [OK]

Common Mistakes:

• Confusing AVERAGEIF with SUM or COUNT functions
• Thinking it handles multiple conditions
• Assuming it finds max or min values

2. Which of the following is the correct syntax for the AVERAGEIFS function?

easy

A. AVERAGEIFS(criteria_range, average_range, criteria1, criteria2)

B. AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

C. AVERAGEIFS(average_range, criteria1, criteria_range1, criteria2)

D. AVERAGEIFS(average_range, criteria_range1, criteria1)

Solution

1. Step 1: Recall AVERAGEIFS syntax

   The correct syntax starts with the range to average, then pairs of criteria ranges and criteria.

2. Step 2: Check option formats

   AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) matches the correct order: average_range, criteria_range1, criteria1, etc.

3. Final Answer:

   AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) -> Option B

4. Quick Check:

   AVERAGEIFS syntax = average_range first [OK]

Hint: AVERAGEIFS starts with average range, then criteria pairs [OK]

Common Mistakes:

• Swapping average_range and criteria_range
• Omitting criteria pairs
• Using AVERAGEIF syntax for AVERAGEIFS

3. Given the data:
A1:A5 = {10, 20, 30, 40, 50}
B1:B5 = {"Yes", "No", "Yes", "No", "Yes"}
What is the result of =AVERAGEIF(B1:B5, "Yes", A1:A5)?

medium

A. 30

B. 20

C. 40

D. 50

Solution

1. Step 1: Identify cells matching criteria

   Cells in B1:B5 with "Yes" are B1, B3, B5 corresponding to A1=10, A3=30, A5=50.

2. Step 2: Calculate average of matching A cells

   Average = (10 + 30 + 50) / 3 = 90 / 3 = 30.

3. Final Answer:

   30 -> Option A

4. Quick Check:

   Average of 10,30,50 = 30 [OK]

Hint: Average only values where criteria matches [OK]

Common Mistakes:

• Averaging all values ignoring criteria
• Using wrong criteria text
• Calculating sum instead of average

4. You wrote the formula =AVERAGEIFS(A1:A5, B1:B5, ">20", C1:C5, "Yes") but it returns #DIV/0!. What is the likely cause?

medium

A. No cells meet both criteria, so division by zero occurs.

B. Syntax error in the formula due to missing commas.

C. AVERAGEIFS cannot handle numeric criteria with ">".

D. The average range and criteria ranges have different sizes.

Solution

1. Step 1: Understand #DIV/0! error in AVERAGEIFS

   This error happens when no cells meet all criteria, so average divides by zero.

2. Step 2: Check formula logic

   If no values in B1:B5 are >20 and C1:C5 equal "Yes" simultaneously, no data to average.

3. Final Answer:

   No cells meet both criteria, so division by zero occurs. -> Option A

4. Quick Check:

   #DIV/0! means no matching data [OK]

Hint: Check if any data meets all criteria before averaging [OK]

Common Mistakes:

• Assuming syntax error causes #DIV/0!
• Thinking AVERAGEIFS can't use > or <
• Ignoring range size mismatch possibility

5. You have sales data in A2:A10, regions in B2:B10, and sales reps in C2:C10. How do you calculate the average sales for region "East" and sales rep "John"?

hard

A. =AVERAGEIF(A2:A10, "East", B2:B10, "John")

B. =AVERAGEIFS(B2:B10, A2:A10, "East", C2:C10, "John")

C. =AVERAGEIFS(A2:A10, B2:B10, "East", C2:C10, "John")

D. =AVERAGEIF(B2:B10, "East") + AVERAGEIF(C2:C10, "John")

Solution

1. Step 1: Identify correct function for multiple conditions

   Use AVERAGEIFS to average with more than one condition.

2. Step 2: Match ranges and criteria correctly

   Average range is sales (A2:A10), criteria ranges are region (B2:B10) and rep (C2:C10) with criteria "East" and "John".

3. Step 3: Verify formula syntax

   =AVERAGEIFS(A2:A10, B2:B10, "East", C2:C10, "John") matches correct syntax and logic.

4. Final Answer:

   =AVERAGEIFS(A2:A10, B2:B10, "East", C2:C10, "John") -> Option C

5. Quick Check:

   AVERAGEIFS with correct ranges and criteria [OK]

Hint: Use AVERAGEIFS for multiple conditions with matching ranges [OK]

Common Mistakes:

• Using AVERAGEIF for multiple conditions
• Swapping average and criteria ranges
• Adding averages instead of filtering both conditions