CODEWITHSUNDEEP
excelexcel-formula
conorgriffin
conorgriffin

Reputation: 4339

How do I get the max value from the sum of two columns in excel?

Given two columns, A and B like so:

   | A  | B
-------------
1  | 6  | 2
2  | 4  | 5
3  | 8  | 2

Can I write an excel formula that will achieve =MAX(A1+B1,A2+B2,A3+B3) without having to type every row into the formula?

Upvotes: 2

Views: 16441

Answers (3)

barry houdini
barry houdini

Reputation: 46341

You can do it with an "array formula" like this

=MAX(A1:A3+B1:B3)

confirm with CTRL+SHIFT+ENTER

You can add an INDEX function to make it a "regular" formula, i.e.

=MAX(INDEX(A1:A3+B1:B3,0))

or a non-array version for Excel 2010 or later versions

=AGGREGATE(14,6,A1:A3+B1:B3,1)

14 indicates LARGE in AGGREGATE function - with the 1 at the end meaning the largest

Upvotes: 9

Chalumeau
Chalumeau

Reputation: 101

In visual Basic, writte this : 

Sub Max_Sum()
Dim col_1, col_2 As Double
Dim Result, Result_Max As Double

Dim nRow As Integer
nRow = 1 'If the started row is the first

Do
    col_1 = Cells(nRow, 1).Value
    col_2 = Cells(nRow, 2).Value
    Result = col_1 + col_2
    If Result > Result_Max Then
        Result_Max = Result
    End If
    nRow = nRow + 1
Loop Until col_1 = "" Or col_2 = ""

'Writte the result in the C3 range
Cells(3, 3).Value = Result_Max
End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Use the Array Formula:

=MAX((A1:A100)+(B1:B100))

This must be entered with CTRL-SHIFT-ENTER rather than just the ENTER key

Upvotes: 3

Related Questions