Find Minimum and Maximum Values by Multiple Criteria in Excel PivotTables
Question details
Find the minimum and maximum values in one data column for each group defined by three separate criteria columns.
- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- The user needs to summarize a dataset by three criteria fields and compare the lowest and highest value for every unique criteria combination.
- Observed behavior
- The desired result is a grouped report that shows both a minimum and a maximum value for each combination of the three criteria.
Make sure the source range has a single header row and no completely blank rows or columns inside the data. Confirm that the value column contains numbers rather than numbers stored as text.
Create a PivotTable with Minimum and Maximum Value Fields
Use this method when you want a refreshable summary that groups data by three criteria and calculates both the lowest and highest value in the same report.
A PivotTable can summarize the same numeric field more than once. Add the field twice, then set one copy to Minimum and the other to Maximum.
Click any cell in the dataset that contains the three criteria columns and the numeric value column.
In Excel, select Insert > PivotTable, confirm the table or range, choose where to place the PivotTable, and select OK.
In the PivotTable Fields pane, drag Criteria Column 1, Criteria Column 2, and Criteria Column 3 to the Rows area in the order you want them grouped.
Drag the numeric value field, such as Column 6, to the Values area two times.
Open the drop-down for the first value field in the Values area, choose Value Field Settings, select Min, and select OK. Repeat for the second value field and select Max.
Calculate Minimum and Maximum Values by Multiple Criteria in WPS Spreadsheet
WPS Spreadsheet lets you build a PivotTable, place multiple criteria fields in rows, and summarize the same value field as both Minimum and Maximum. The familiar spreadsheet interface makes it easy to review grouped results and refresh them when source data changes.
- 1. Select your dataset: In WPS Spreadsheet, click a cell within the data range that includes the criteria columns and numeric value column.
- 2. Create a PivotTable: Go to Insert > PivotTable, verify the selected range, choose a destination worksheet, and confirm.
- 3. Arrange the criteria fields: In the PivotTable Fields panel, drag the three criteria columns to the Rows area.
- 4. Add the numeric field two times: Drag the value column to Values twice so that two separate summary fields are available.
- 5. Choose Min and Max: Open each value field setting in the Values area, set one calculation to Min and the other to Max, then confirm the report.
Frequently Asked Questions
Can a PivotTable calculate both the minimum and maximum for the same column?
Yes. Drag the same numeric field into the Values area twice. Open Value Field Settings for each copy, then choose Min for one and Max for the other.
How do I group PivotTable results using three columns?
Drag all three criteria columns into the Rows area of the PivotTable Fields pane. Their order controls the nesting order of the groups.
Why does Excel show Count instead of Min or Max in my PivotTable?
The source value column may contain text, blanks, or numbers stored as text. Convert the entries to real numbers, refresh the PivotTable, and then choose Value Field Settings > Min or Max.
Do I need formulas such as MINIFS and MAXIFS for this report?
No. A PivotTable is usually simpler when you need results for every combination of several criteria. MINIFS and MAXIFS are more useful when you need a formula for a specific criteria combination in a worksheet cell.
