Find the Lowest Value in a Category

Excel & Google Sheets

How it works

MINIFS is the mirror image of MAXIFS: it scans the amounts in column C, keeps only the rows where column A says Sales, and returns the smallest of those. The range to minimize comes first, then pairs of criteria range and criteria — add more pairs to narrow further, like MINIFS(C:C,A:A,"Sales",C:C,">0") to ignore zero rows. Because dates are numbers, MINIFS also finds the EARLIEST date per category, which makes it handy for "next deadline per project" columns. It works in Google Sheets and Excel 2019 or newer, and returns 0 when nothing matches.

C:C

The numbers to find the minimum of — the Amount column.

A:A

The column to test — the Department column.

"Sales"

The category that rows must match. A cell reference like E2 works too.

When to use it

Use this to find the cheapest quote per supplier, the lowest score per team, or the earliest due date per project. Combined with MAXIFS you can show each category's full range in two columns.

Common mistakes

• ✕Zeros or blanks drag the minimum down to 0.

  ✓Unfilled amount cells count as 0. Exclude them with a second condition: =MINIFS(C:C,A:A,"Sales",C:C,">0").

• ✕MINIFS returns #NAME? in older Excel.

  ✓MINIFS needs Excel 2019 or newer. In Excel 2016 and earlier, use the array formula =MIN(IF(A:A="Sales",C:C)) entered with Ctrl+Shift+Enter.

• ✕It returns 0 because the criteria never matches.

  ✓A spelling difference or trailing space in the category column means no rows qualify. TRIM the column and match the exact wording — or reference a cell that holds the category.