How to Average by Category in Excel with AVERAGEIF

Before scheduling repairs or writing a weekly report, calculate average repair time by category. Open your Excel work order sheet (.xlsx) in Cue Office and use AVERAGEIF to average the minutes for the category you select. A recorded zero counts in the average. A blank time is skipped.

How Do You Average Repair Time by Category?

In this example, column B contains the repair Category, column C contains the duration in Minutes, cell E2 specifies the category to summarize, and cell F2 displays the average. To follow along, paste the practice data into A1 of a new worksheet, where E2 is set to Lighting.

Work order table showing six records, C4 with numeric 0, selected C5 empty, and F2 empty
Among the Lighting tickets, M03 shows a duration of 0, while M04 is blank.

Copy the formula below, select F2, and paste it. B2:B7 is the category range, E2 is the category to match, and C2:C7 is the duration range for the same work orders.

=AVERAGEIF(B2:B7,E2,C2:C7)

When E2 is set to Lighting, the durations for M01, M03, and M05 are 12, 0, and 24 minutes. The formula sums these values and divides by 3, yielding an average of 12 minutes. M04 also belongs to Lighting, but its duration cell C5 is blank, so it is not included in the count.

E2 shows Lighting, F2 shows 12, formula bar shows AVERAGEIF, C4 is 0, and C5 is blank
Select F2 to verify the category range and duration range in the formula bar.

Why Does Filling a Blank with Zero Change the Average?

Enter the number 0 into C5. The total duration for Lighting remains 36 minutes, but the count of tickets included in the average increases to four, changing F2 to 9.

After entering 0 in C5, the Lighting average repair time changes to 9
When both C4 and C5 are included in the average, F2 displays 9.

Clear the 0 you just entered in C5 and verify that F2 returns to 12. Next, change E2 to Plumbing. The formula takes the 30 and 20 minutes from M02 and M06, resulting in 25. Save and reopen the workbook to verify the category, result, and formula.

After reopening the manual workbook, E2 is Plumbing, F2 is 25, and C5 is blank again
After reopening, the average repair time for Plumbing remains 25 minutes.

How Can You Check That Cue AI Updates the Average?

Open an independent practice copy, ensuring that E2 is set to Lighting and F2 is empty. Click Cue AI on the Home tab.

Cue AI panel open beside six repair tickets, with E2 showing Lighting and F2 empty
After opening Cue AI, check the original tickets and empty F2 next to the panel.

Send the prompt below to clearly specify the calculation rules, destination cell, and dynamic update requirement.

What is the average repair time for the category selected in E2? Include recorded times of zero minutes and skip rows where the time is blank. Put the result only in F2 and make it update when I change the category in E2. Please keep the original work orders, times, category selection, and headings as they are.

Cue AI wrote an AVERAGEIFS formula in F2 only. It uses E2 to match categories in column B, then averages the repair times from those rows. Save, close, and reopen the copy. Select F2 to check the formula and the 12-minute Lighting average. C4 still contains a numeric zero, and C5 is blank.

Formula bar shows AVERAGEIFS on reopen, Lighting average time is 12, C4 is 0, and C5 is blank
After saving and reopening, the average repair time for Lighting remains 12 minutes.

In the reopened copy, change E2 from Lighting to Plumbing. F2 updates to 25 minutes, the average of the two Plumbing work orders at 30 and 20 minutes.

In the same reopened session, E2 is changed to Plumbing and F2 displays 25
Switching to Plumbing in the reopened copy updates the average repair time to 25 minutes.

Practice with Work Order Data

Show the full example

Create a new spreadsheet in Cue Office and paste the following data into A1. Leave the duration in row M04 blank. Then return to the formula steps to calculate the value starting in F2.

Ticket	Category	Minutes		Category	Avg minutes
M01	Lighting	12		Lighting
M02	Plumbing	30
M03	Lighting	0
M04	Lighting
M05	Lighting	24
M06	Plumbing	20

Check Missing Repair Times Before Sending the Weekly Report

Before you summarize this week’s repairs, tell Cue AI which column holds the categories, which column holds the repair times, and which category to average. When Lighting averages 12 minutes, check one work order recorded as zero and one with no time entered. Ask the person on shift to fill in the missing time, then update the report.

If a contractor sends a work order receipt (.pdf), open it in Cue Office for free and match the work order number to your Excel repair sheet (.xlsx) to verify the time. Record missing times and who will check them in a Word handoff log (.docx). In the PowerPoint weekly report (.pptx), list the average repair time by category and how many work orders still need a time entry. Cue Office offers free spreadsheet, document, and presentation editing.

Download Cue Office for Free