How to Sum Values from the First Minimum or Maximum Row in Excel
Question details
The user wants to calculate the sum of values in a target column starting exactly from the row where a minimum or maximum value appears in adjacent columns, extending down to the last row.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a dynamic sum range that automatically adjusts its starting point based on the location of the dataset's lowest or highest values.
- Observed behavior
- A specific formula combining SUM, INDEX, MIN, and MAX functions is needed to identify the row number of the first target value and build the correct range.
Verify the exact dimensions of your dataset and ensure you know which columns contain the criteria (the min/max values) and which column contains the numeric values you wish to sum.
Use a Combination of SUM, INDEX, and MIN/MAX Array Formula
This method uses an array formula to locate the first instance of a minimum or maximum value in your specified columns, then dynamically creates a range to sum the corresponding values in your target column.
By nesting an IF statement inside a MIN function, you can determine the exact row number where the dataset's minimum or maximum value first appears. The INDEX function then converts this row number into a valid starting cell reference for your SUM function.
Click on the empty cell where you want the final sum to be displayed.
Type the formula =SUM(INDEX(C1:C4,MIN(IF((A1:B4=MIN(A1:B4))+(A1:B4=MAX(A1:B4)),ROW(A1:B4)))):C4). If your data spans 5 rows, adjust the ranges to A1:B5 and C1:C5 respectively.
If you are using an older version of Excel (prior to Microsoft 365 or Office 2021), press Ctrl+Shift+Enter simultaneously to evaluate the function properly. You will see curly brackets {} appear around the formula.

Handle Complex Array Formulas Seamlessly with WPS Spreadsheet
WPS Office perfectly supports complex array formulas, dynamic data ranges, and nested functions like INDEX and MATCH. You can use exactly the same formulas as you would in Microsoft Excel.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your dataset.
- 2. Enter the formula: Select the target cell, paste your dynamic SUM and INDEX formula, and adjust the cell references to match your data.
- 3. Calculate the result: Press Ctrl+Shift+Enter to compute the array formula and get the accurate sum immediately.

Frequently Asked Questions
Why does my formula return a #VALUE! error?
This usually occurs in older versions of Excel when you press Enter instead of Ctrl+Shift+Enter. Array formulas require the Ctrl+Shift+Enter keystroke to evaluate the multiple data points correctly.
How do I apply this formula to a much larger dataset?
You only need to update the row numbers in the ranges. For instance, if your dataset spans down to row 100, update C1:C4 to C1:C100, update A1:B4 to A1:B100, and change the final reference after the colon to C100.
Can I sum a different column without changing the minimum/maximum criteria?
Yes. To sum a different column, simply change the C1:C4 and :C4 references to the target column's letter (e.g., D1:D4 and :D4) while keeping the A and B column references exactly the same.




