Find the maximum value with criteria in Excel (MAXIFS)
=MAXIFS(C2:C9,B2:B9,F1)Excel · result on the sample sheet: $3,395
Describe what you need and get a working MAXIFS formula for Excel and Google Sheets, tested on real data before you copy it. MAXIFS returns the largest value among the rows that meet one or more conditions: the biggest deal in a region, the latest order date for a customer.
Each formula below was calculated on its own sample sheet. Open one to see the sheet, why it works, and to try it yourself.
=MAXIFS(C2:C9,B2:B9,F1)Excel · result on the sample sheet: $3,395
=MAXIFS($B$2:$B$9,$A$2:$A$9,E2)Excel · result on the sample sheet: 3 rows (F2:F4)
=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1)),C2:C9)Excel · result on the sample sheet: $1,460
=FILTER(A2:C12,B2:B12=MAXIFS(B2:B12,A2:A12,A2:A12))Excel · result on the sample sheet: 4 rows (E2:G5)
=SORT(FILTER(A2:C11,B2:B11=MAXIFS(B2:B11,A2:A11,A2:A11)))Excel · result on the sample sheet: 4 rows (E2:G5)
=HSTACK(UNIQUE(A2:A13),MAXIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))Excel · result on the sample sheet: 3 rows (E2:F4)
=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1)),C2:C9)Excel · result on the sample sheet: $27.90
=XLOOKUP(1,($B$2:$B$10=E2)*($C$2:$C$10=MAXIFS($C$2:$C$10,$B$2:$B$10,E2)),$A$2:$A$10)Excel · result on the sample sheet: 4 rows (F2:F5)
You describe the task in plain English, and it writes the formula, using MAXIFS where that's the right tool, for your table or a sample one. The formula is run in our engine in Excel and Google Sheets mode before you see it, so you get its result, not just a guess.
MAXIFS returns the largest value among the rows that meet one or more conditions: the biggest deal in a region, the latest order date for a customer.
Yes. Each example below opens a page where you try the formula in a live grid and it's checked as you go, and the MAXIFS function page has a practice exercise.