How to Use the AVERAGEIFS Function in Excel

The AVERAGEIFS function allows you to calculate averages based on one or more conditions in your dataset. This makes it extremely useful for business analysis, where you often want to measure performance by category, employee, or service type. In this lesson, you’ll learn how the AVERAGEIFS function works and see how it’s used to calculate average service ticket revenue by job type, technician, and a combination of both using HVAC service call data.

Download the Excel file used in this tutorial:

Step By Step Instructions

The AVERAGEIFS Function

1. Identify the metric you want to average

Start with a dataset that contains operational records such as jobs, parts cost, revenue, labor hours, and technician assignments
Decide which numeric column you want to analyze, such as revenue per job or repair ticket value
This numeric column becomes the average range used in the calculation

2. Create a summary list of categories to analyze

Build a simple list of categories you want to evaluate
Examples from the walkthrough include:
- Job type (repair, maintenance, emergency, install estimate)
- Technician names
These categories will become the criteria used to calculate averages

3. Calculate the average value by job type

Use the AVERAGEIFS function
Select the revenue column as the numeric range being averaged
Use the job type column as the criteria range
Reference the job type from your summary table
Copy the formula down to return the average ticket value for every job type

4. Calculate the average value by technician

Repeat the same process using AVERAGEIFS
Keep the revenue column as the value being averaged
Change the criteria range to the technician column
Reference the technician name from your summary list
Drag the formula down to calculate the average ticket value for each technician

5. Add a second condition to refine the analysis

Use AVERAGEIFS again, but now include multiple criteria
Keep the revenue column as the numeric range
Add job type as the first condition
Add technician as the second condition
This calculates the average revenue for a specific technician performing a specific job type

6. Lock references so formulas copy correctly

Use absolute referencing when copying formulas across rows and columns
Press F4 to lock ranges that should remain fixed
This ensures the revenue range and criteria ranges stay consistent as the formula is copied

7. Copy the formula across the full analysis table

Drag the formula across technicians and down job types
Excel automatically calculates the average revenue for every technician and job combination
This creates a matrix of average ticket values across multiple conditions

8. Use the results to compare operational performance

Identify which job types generate the highest average revenue
Compare technician performance on similar job categories

Quickly spot patterns such as technicians who generate higher ticket values on certain service types

FAQ

The AVERAGEIFS Function in Excel

Q1. What does the AVERAGEIFS function do in Excel?
The AVERAGEIFS function calculates the average of a group of numbers that meet specific criteria. It allows you to filter the data before calculating the average, making it ideal for analyzing performance across categories, teams, or time periods.

Q2. What is the difference between AVERAGEIF and AVERAGEIFS?
The AVERAGEIF function allows you to calculate an average using only one condition. The AVERAGEIFS function allows multiple conditions, which makes it much more flexible for real-world data analysis.

Q3. When should I use the AVERAGEIFS function?
Use AVERAGEIFS when you want to calculate averages for a subset of your data. For example, you might want to calculate the average revenue for emergency service calls, the average ticket per technician, or the average revenue for a specific technician and job type.

Q4. Why is AVERAGEIFS useful for business analysis?
Business data often needs to be analyzed by different dimensions such as employee, service type, or location. AVERAGEIFS allows you to quickly break down averages across these dimensions to uncover insights about performance and profitability.

Q5. What types of data work best with AVERAGEIFS?
AVERAGEIFS works best with structured datasets where you have numeric values (such as revenue or cost) and categorical columns (such as technician, job type, or department) that you can use as criteria.

Q6. Can AVERAGEIFS use more than two conditions?
Yes. One of the biggest advantages of AVERAGEIFS is that it supports multiple criteria, allowing you to analyze very specific segments of your data by combining several filters in the same formula.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Databoards

Analysis & Development