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

  • AVERAGEA Function – Google Sheets

    The AVERAGEA calculated the mean of a set of values similar to AVERAGE, but offers additional functionality. When all inputs to the function are numbers, it produces the same result as the AVERAGE function. However, when the inputs are text or boolean values, the AVERAGEA function acts differently than AVERAGE. Get your copy of the template used in…

  • AVERAGE Function – Google Sheets

    The AVERAGE function calculates the mean of a set of values in your spreadsheet while ignoring any non-number values. See AVERAGEA if you want to average boolean values or count text as zero instead of ignoring it. Make a copy of this spreadsheet template to follow along with the examples. Contents1 Purpose2 Syntax3 Similar Functions4…

  • SUBTOTAL Function in Google Sheets

    SUBTOTAL deserves more attention than it gets. Despite its robustness, it is far less prevalent than functions such as SUM and AVERAGE. Let’s look at what you can do with this function in Google Sheets using this template to follow along. Contents1 Purpose2 Video Explanation3 Syntax4 Alternative to SUBTOTAL4.1 Example 1 – Summing a Range with Subtotals4.2 Example 2 –…