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.
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.
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.
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.
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.
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.
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.
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.
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.