User Rating: 3 / 5     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.

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

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.