Print

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

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

 


 

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

   

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. 

  

 

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

 

Related Formulas - illustrated in the Ebook of Excel Formulas.

 

 

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.