Function Problems

Filter the Minimum Value for Each Group in Excel

Nimra MalikNimra Malik Nov 18, 2026 869 views

Question details

Find the lowest value for each group after filtering rows by a selected criterion.

Filter the Minimum Value for Each Group in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A table contains group names in the first column, filter criteria in the middle column, and values in the third column.
Observed behavior
The desired result contains rows matching the selected criterion whose value is the minimum for their group.
Before you start

For the examples below, place headers in C1:E1, source data in C2:E9, and the selected middle-column criterion in A1. Make sure column E contains numeric values and column F is available for a helper column.

Solution 1Recommended

Filter the minimum for each group with a helper column

Use MINIFS and Excel's filter controls to show matching minimum rows in the original table.

1
Enter the criterion

Type the value to match against column D in cell A1.

2
Add a helper formula

Enter Keep in F1. In F2, enter =AND(D2=$A$1,E2=MINIFS($E$2:$E$9,$C$2:$C$9,C2,$D$2:$D$9,$A$1)).

3
Fill the formula down

Select F2 and drag its fill handle down through F9. Adjust the formula ranges if your source data extends beyond row 9.

4
Filter the results

Select C1:F9, choose Data > Filter, open the filter arrow in F1, and select TRUE.

Filter the minimum for each group with a helper column
Ties and changed criteria: All rows tied for a group's minimum are retained. If you change A1, reapply the filter to refresh the visible rows.
Work with grouped data in WPS

Filter group minimums in WPS Spreadsheet

Use a MINIFS helper column and the filter controls in WPS Spreadsheet to keep the lowest matching value for each group.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and arrange the example data in C2:E9, with headers in C1:E1.
  2. 2. Enter the criterion: Type the middle-column value you want to match in A1.
  3. 3. Create the helper column: Enter Keep in F1, then enter =AND(D2=$A$1,E2=MINIFS($E$2:$E$9,$C$2:$C$9,C2,$D$2:$D$9,$A$1)) in F2 and fill it down through F9.
  4. 4. Filter matching minimums: Select C1:F9, choose Data > Filter, and use the F1 filter arrow to display TRUE rows. Reapply the filter after changing A1.
Filter minimum values for each group using a selected criterionOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and filter controlsWork with spreadsheets in the free WPS Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Will the filter keep more than one row if the minimum is tied?

Yes. Both formulas return every matching row whose value equals the minimum for its group.

How do I change the middle-column criterion?

Enter the new criterion in A1. The dynamic array updates automatically; for the helper-column method, reapply the filter in F1.

Why does the dynamic array formula show #SPILL!?

Clear any occupied cells in the result area below or to the right of the formula, then check that the formula is outside the source data.

What if my table has more than eight data rows?

Extend every source range consistently, such as changing C2:E9 to C2:E100 and D2:D9 to D2:D100. For the helper method, also fill the formula down to the last data row.