Finding the absolute lowest number in a massive Google Sheet is incredibly easy: you just use the =MIN() function. However, real-world data analysis is rarely that simple. What if you have a spreadsheet containing thousands of sales transactions across three different countries, and you need to find the lowest sale amount only for the “Canada” region, while completely ignoring the USA and Mexico data?
If you try to sort and filter the data manually, it takes too much time and breaks the second new data is added. To calculate this dynamically, you must use the MINIFS function. It allows you to define strict rules (criteria) that Google Sheets must check before it decides whether a number is allowed to compete for the title of “lowest value.”
The Syntax of MINIFS
The formula requires three distinct pieces of information:
=MINIFS(range_to_find_minimum, range_to_check, criteria_to_match)
Step 1: Understand the Data Structure
Assume your spreadsheet is set up as follows:
- Column A: Region (e.g., “USA”, “Canada”, “Mexico”)
- Column B: Sale Amount (e.g., $500, $120, $950)
Our goal is to find the smallest dollar amount in Column B, but only for rows where Column A says “Canada”.
Step 2: Write the Formula
- Click on an empty cell where you want your final answer to appear (e.g., D2).
- Type the formula exactly like this:
=MINIFS(B:B, A:A, "Canada") - Press Enter.
How the Formula Works
Let’s break down the logic of what Google Sheets is doing in the background:
B:B(The Math Range): This tells the formula, “When you are ready to find the smallest number, look at all the money values in Column B.”A:A(The Checking Range): This tells the formula, “Before you look at the money, look at the text in Column A.”"Canada"(The Strict Rule): This is the bouncer at the door. As the formula scans down the sheet row by row, it looks at Column A. If A2 says “USA”, the formula instantly rejects the row and ignores the money in B2. If A3 says “Canada”, the formula accepts the row, grabs the money in B3, and throws it into a temporary pile.
Once it reaches the bottom of the sheet, it looks at the temporary pile of only Canadian sales, finds the smallest number in that specific pile, and prints the result on your screen.
Advanced Usage: Multiple Criteria
The true power of MINIFS is that you can stack multiple rules. What if you want the lowest sale in Canada, but only for the “Q1” quarter (assuming Column C is the Quarter)?
You simply chain the ranges and rules together:
=MINIFS(B:B, A:A, "Canada", C:C, "Q1")
Google Sheets will now only consider a number if it passes both strict criteria, giving you hyper-specific, automated data extraction without ever needing to use a Pivot Table.