User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

SUM Max values of Each Row, in a Multiple Column Range

 

 

Related Links: 

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

 


 

Return SUM of Max values in Each Row, for a Multiple Column Range.

 

 

Cell E4 formula: Determines the MAX value of each row in the 2 column range A2:B10, and returns the SUM of each of these MAX values. 

   

 

-----------------------------------------------------------------------------------------------

 

 

Sum Max Value in Each Row 

           

"Open Image in New Tab" for a full and clear view. 

 

 

Cell E4 formula: Determines the MAX value of each row in the 2 column range A2:B10, and returns the SUM of each of these MAX values.

 

This formula can be used for 2 columns specifically.

 

 

Cell E4 formula: 

  

=SUM(IF( A2:A10>= B2:B10, A2:A10, B2:B10))  

  

Explanation - Cell E4 formula (Array formula - Ctrl+Shift+Enter): 

 

1) IF(A2:A10>= B2:B10, A2:A10, B2:B10): returns an array of values and zeros - {7; 13;8; 9; 4;7;0; 0;0} - the values are either (i) from column A if its value is equal to or greater than column B in the corresponding row, or (ii) from column B if its value is greater than column A in the corresponding row. For blank cells in both columns it returns a zero. Using SUM returns the total of each of these higher values ie. 48. 

  

 

 

Other formula(s): To consider MAX value in each Row for a 2 or more columns Range, using the Offset function.

 

 

Cell E7 formula: Determines the MAX value of each row in the 2 column range A2:B10, and returns the SUM of each of these MAX values. Normal, Non-Array formula.

 

Cell E8 formula: Determines the MAX value of each row in the 3 column range A2:C10, and returns the SUM of each of these MAX values. Normal, Non-Array formula.

 

 

Note: While cell E4 formula can be used only for 2 columns specifically, the cell E7 / E8 formula can be used for any number of columns (2 or more), to return the SUM Max values of Each Row, so that a Multiple Column Range can be of 10 or 20 columns also.

 

 

    

Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.