MAXIFS in Google Sheets: Find the Largest Eligible Worksheet Value

• Introduced: first launch date or introduction version not established by the official history reviewed.
• Availability: documented in the rolling Google Sheets service; this guide uses ordinary cells in the web editor, without a claim about identical mobile authoring workflows.
• Checked: October 14, 2026 against the official MAXIFS reference. Examples were independently calculated; no native Sheets execution was performed.

=MAXIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2], ...)

The value range and first condition pair are required. Additional pairs restrict the same set of positions. Google requires equal-sized ranges and documents a #VALUE! size-mismatch error. Official MAXIFS reference

The page also contains a separate MAXIFS for BigQuery section using data columns. This tutorial uses ordinary web-sheet cells such as D2:D9; it does not assert identical authoring syntax or behavior for connected BigQuery columns.

Enter the following in A1:D9. Row 1 is the header, and D2:D9 contains actual numbers.

Keep formulas outside the source rectangle. All examples exclude the header and use all eight data rows.

=MAXIFS(D2:D9,B2:B9,"North")
=MAXIFS(D2:D9,B2:B9,"North",C2:C9,"Ready")

Expected results: 40 and 25. The first includes K03. The Ready condition excludes that held record, leaving 18, 25, 0 and 25.

The function returns the largest value, not a unique item ID. K04 and K08 both achieve 25. A later lookup needs an explicit policy for ties rather than assuming that a maximum identifies one row.

=MAXIFS(D2:D9,B2:B9,"North",C2:C9,"Ready",D2:D9,"<20")

Expected result: 18. The North Ready records below 20 have values 18 and zero. Requiring the same region to equal both North and South in separate pairs would not implement OR; criteria intersect.

For an adjustable strict upper bound of 20 in F2, use "<"&F2 as the final criterion. Changing F2 to 26 makes the expected maximum 25. The operator is joined to the parameter’s current value; quoted text "<F2" would not substitute that cell value.

=MAXIFS(D2:D9,B2:B9,"North",D2:D9,"<1")
=MAXIFS(D2:D9,B2:B9,"East")

Expected results: 0 and 0, for different reasons. The first selects K06’s actual zero. The second selects no rows. Google’s reference explicitly documents the no-match result, so an error handler alone cannot distinguish these situations.

For an East summary with a visible no-match label:

=IF(COUNTIFS(B2:B9,"East")=0,"No matching rows",MAXIFS(D2:D9,B2:B9,"East"))

Expected result: No matching rows. COUNTIFS mirrors the selection. If you add more criteria to MAXIFS, apply the same criteria to its count check. This sample has fully numeric units; matching-row counts are not a substitute for validating imported values.

Enter North, South, West, East in F5:F8. In G5 enter and fill down:

=MAXIFS($D$2:$D$9,$B$2:$B$9,F5,$C$2:$C$9,"Ready")

Expected results: 25, 12, 16, 0. The dollar signs fix the source ranges while F5 changes with each report row. The final zero needs a count flag if readers must distinguish absent records from real zeros.

A visible filter applied to the table is not written into this formula’s criteria. If a field determines inclusion, make that condition explicit instead of relying on which rows currently appear on screen.

These differently sized ranges are not compatible:

=MAXIFS(D2:D9,B2:B8,"North")

Expected result: #VALUE! under Google’s documented size rule. The value range has eight rows, while the condition range has seven.

Even equal-sized ranges can represent the wrong records if one begins on a different row. For ordinary record tables, align their first and last rows as well as their dimensions. A formula returning a plausible number is not evidence that the intended records were paired.

Use actual numeric units, explicit source bounds and a visible criterion table while testing. The Google page does not establish the same maximum criterion-pair count, blank-criterion convention or coercion matrix as Excel; those details are not assumed here.

Keep the raw records available with the summary. If a result seems wrong, list the qualifying rows and calculate their ordinary maximum independently. That reveals a mistaken condition more directly than adding a broad error handler around the complete formula.

Verification: Expected results use fictional data and independent calculations rather than native Google Sheets observations. See the editorial testing standards.