Formula Errors

How to Sum Values from the First Minimum or Maximum Row in Excel

Olivia MillerOlivia Miller Oct 20, 2026 872 views

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.

How to Sum Values from the First Minimum or Maximum Row in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click on the empty cell where you want the final sum to be displayed.

2
Input the array formula

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.

3
Evaluate as an array

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.

Use a Combination of SUM, INDEX, and MIN/MAX Array Formula
Dynamic Array Support: If you are using Microsoft 365 or Office 2021, the new dynamic calculation engine allows you to simply press Enter without needing the Ctrl+Shift+Enter keystroke.
Advanced Formula Processing

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. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your dataset.
  2. 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. 3. Calculate the result: Press Ctrl+Shift+Enter to compute the array formula and get the accurate sum immediately.
100% compatible with Microsoft Excel formulas and functionsNative support for advanced array formulas and dynamic rangesLightweight, fast, and completely free to download
QA img-9

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.