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.