Finding the absolute highest or lowest number in a massive Google Sheets dataset is incredibly easy: you just use the standard =MAX(A:A) or =MIN(A:A) formulas. However, real-world data analysis is rarely that simple.
What if you are looking at a 10,000-row global sales sheet, and you don’t just want the highest sale ever recorded; you want the highest sale specifically within the “Europe” region, that was completed in “Q3”, and sold by “John Smith”? Trying to manually filter the sheet and eyeball the highest number is a recipe for disaster.
To extract absolute extremes based on incredibly specific, multi-layered criteria, you must use the MINIFS and MAXIFS functions.
The Syntax of MAXIFS and MINIFS
Both formulas share the exact same structural syntax, which can be slightly confusing because it requires you to declare the “answer” column first, before you declare your rules.
=MAXIFS(range_to_find_max_in, criteria_range_1, "Rule 1", [criteria_range_2, "Rule 2", ...])
A Practical Example: The Sales Database
Imagine your data is laid out like this:
- Column A: Sales Rep Name
- Column B: Region (e.g., North, South, East, West)
- Column C: Total Sale Value ($$)
We want to find the absolute largest sale (Max) ever made specifically in the “South” region.
Step 1: Write a Single-Condition MAXIFS
- Click on an empty cell where you want the answer to appear.
- Type the start of the formula:
=MAXIFS( - Select the column where the actual dollar amounts live (the numbers we are evaluating):
C:C, - Select the column where the region data lives:
B:B, - Type the exact rule you want to enforce, in quotation marks:
"South")
The Final Formula: =MAXIFS(C:C, B:B, "South")
Google Sheets will instantly scan the entire document, completely ignore any rows where Column B says North or East, look only at the South rows, and output the single highest dollar amount from Column C.
Step 2: Adding Multiple Conditions
Now let’s find the absolute lowest sale (MIN) made in the “South” region, but only if the sale was made by “Sarah”. We just chain the arguments together.
The Formula: =MINIFS(C:C, B:B, "South", A:A, "Sarah")
How it works:
C:C= Look at the dollar amounts.B:B, "South"= Rule 1: The region must be South.A:A, "Sarah"= Rule 2: The rep must be Sarah.
Google Sheets applies both filters simultaneously behind the scenes and spits out Sarah’s worst day in the South region. You can chain up to 126 different conditions together in a single formula, allowing you to slice through massive enterprise datasets with surgical precision.