Conditionally Find the Smallest Value | Google Sheets

Adam Steinfurth

Spreadsheets allow you to make sense of complex sets of numbers quickly. In this post, we’ll give you the skills to find the smallest value in your data while filtering the values before you evaluate them. We’ll use a real-world example of examining inventory. We have a list of Prices in column A and a list of their Status in column B.

Inventory listing with two columns. One column with price, one with status.
Inventory

We want to find the lowest price for our In Stock items. Therefore, we need a way to filter the values so we can only consider those In stock before we look at the prices.

The formula finding the smalles value of in stock items
The Smallest Price of the In Stock Items

=MINA(FILTER(A2:A6, B2:B6="In Stock"))

This formula combines the MINA function with the FILTER function in Google Sheets.

Here’s how it works step-by-step:

  1. The FILTER function evaluates the range A2:B6 and includes the rows where the text in B2:B6 is “In Stock.”
  2. This filtered range of values from column A is then passed as the argument to the MINA function.
  3. MINA will then find the minimum value across that filtered range from column A.

So, in essence, this formula is:

  • Identifying the products that are “In Stock” by looking at the values in column B
  • Taking only the prices for those in-stock products from column A
  • And then finding the lowest price from that filtered set of in-stock prices

This can be useful when determining the minimum value, but only for a specific subset of the data. In this case, it’s finding the lowest price among the In-stock items.

The FILTER function allows you to apply conditional logic to select the relevant data before passing it to MINA (or other functions). This makes the MINA result more targeted and meaningful.

Make a copy of the spreadsheet used in this example to adapt the formulas to your spreadsheet.

Related Tutorials