How to Use Excel MAXIFS with Two Criteria

Open an Excel repair log (.xlsx) in Cue Office and use MAXIFS to find the longest repair among rows that match both a site and a device. For North and Radio, the longest repair took 55 minutes. The 120-minute job in the log was for a lamp.

How do you find the maximum value matching two criteria on the same row?

Create a spreadsheet in Cue Office and paste the practice data into A1. Columns A, B, and C hold Site, Device, and Minutes. The example starts with North in F1 and Radio in F2.

Repair log A1:C8 with North and Radio criteria, result in F3 left blank
The starting table shows North and Radio on the right, with F3 still blank.

Paste the formula below into F3. C2:C8 contains the repair times, A2:A8 matches the site in F1, and B2:B8 matches the device in F2. Use ranges of the same size that start and end on the same rows. In this example the three ranges run from row 2 through row 8, so each position refers to the same repair.

=MAXIFS(C2:C8,A2:A8,F1,B2:B8,F2)

The two Radio records for North are 35 and 55 minutes, so F3 returns 55. The Lamp at North took 120 minutes, but the device differs. The Radio at South took 90 minutes, but the station differs. Neither is included in the comparison.

F3 displays 55 under North and Radio, MAXIFS visible in formula bar
Comparing only rows matching both criteria yields a maximum of 55 minutes.

When changing criteria returns 0, how do you know if there was a match?

Keep Radio in F2, change F1 to South, and F3 updates to 90. Then change F2 to Lamp, and F3 updates to 45. Changing one criterion at a time lets you check its impact independently.

F3 displays 45 after changing criteria to South and Lamp
Switch the station first, then the device. South and Lamp return 45.

Change F2 to Toaster. F3 shows 0. Look for a row that has both South and Toaster. This log has none, so the zero means there was no match. In your own log, if you find matching rows, check whether their Minutes column actually contains a zero and whether zero is the largest matching value.

No rows match South and Toaster, F3 displays 0
There are no South Toaster records in the table; 0 here indicates no matching rows.

Restore the criteria to North and Radio. Use Save As to save the file, then close and reopen it. F3 still displays 55; click it to verify the formula in the formula bar.

After saving and reopening the manual copy, the North and Radio result remains 55
Reopening the manual copy keeps the original criteria and formula intact.

How do you have Cue AI write a dynamic formula only in F3?

Open a copy of the repair table where F3 is still blank and click Cue AI. Specify that both criteria must be met at the same time, indicate which cell the result belongs in, and request that the original log and criteria remain intact.

Cue AI button and left panel open, with North and Radio criteria visible on the right
After opening Cue AI, the prompt panel appears on the left, with both selected criteria on the right.
For the repair station and device I picked in F1 and F2, find the longest repair time in minutes among jobs that match both. Put a formula in F3 so the result updates when I change either selection. Please leave the repair log and both selections as they are.

Cue AI wrote MAXIFS in F3 and left the log and both criteria as entered. Changing F1 to South while F2 is still Radio gives 90. Changing F2 to Lamp then gives 45. Both changes recalculate the result.

F3 updates to 45 after switching to South and Lamp in the AI copy
In this AI copy, switching the station and device updates the result to 45.

Restore North and Radio, use Save As to save, then close and reopen the workbook. F3 still displays 55. Click F3 to check the formula bar, confirming the range remains rows 2 to 8 and the criteria reference F1 and F2.

AI copy reopened showing 55 for North and Radio, formula bar displaying MAXIFS with locked row numbers
After reopening the AI copy, F3 still shows 55, with the formula referencing rows 2 through 8.

Practice with seven repair records

Show the full example

Paste the data below into A1. The two criteria will appear in F1 and F2, leaving F3 for the formula. Return to formula setup.

Site	Device	Minutes		Site	North
North	Radio	35		Device	Radio
North	Radio	55		Max min	
North	Lamp	120
South	Radio	90
South	Lamp	45
North	Lamp	40
South	Radio	60

Check repair times by station and device during shift handover

For a shift handoff, ask Cue AI for the longest repair for a specific site and device. Check the repair log before recording the result. For example, file North’s 120-minute job under lamps. Flag combinations with no entries so the next shift knows which records to check.

Check the Excel repair log (.xlsx) in Cue Office, then record jobs that need a second look and combinations with no entries in the Word handoff note (.docx). For a pre-shift briefing, use a PowerPoint slide (.pptx) to list the devices to review at each station. For a repair ticket in PDF format (.pdf), open it for free to check the station, device and recorded time. Cue Office offers free spreadsheet, document and presentation editing.

Download Cue Office for free