Finding Maximum of Two Numbers in Array

[cardan]

Hello all,

I am having trouble writing a formula trying to find the Maximum of
two numbers in a row based upon a value in the same row. Essentially,
I have 3 columns. Column A has values between 1-5. Column B has
values between 0 and lets say 50. Column C also has values between 0
and 75. (same has Column B). These columns have around 100 rows of
data.

If say cell A1 equals 2, I need to add cell C1 and cell B1 together. I
need to do this for all the rows and then I need the formula to return
the maximum of columns B and C.

A1 = 2
A2 = 3
A3=2
A4=5
A5=4

B1 = 3
B2 = 4
B3 = 6
B4= 9
B5=2

C1 = 3
C2=3
C3=2
C4=4
C5=8

I need the formula to recognize the 2 in A1 and A3, and then compute
B1+C1=6 and also compute B3+C3=8 and then return the MAX of these two
which in this case equals 8.

Is this possible? Any help or guidance would greatly be appreciated.
Thank you in advance.

Respectfully, Dan

 

[Rick Rothstein \(MVP - VB\)]

Try this....

=SUMPRODUCT(MAX((A1:A100=2)*(B1:B100+C1:C100)))

although I would replace the 2 with a cell reference and put the lookup
number in it. For example, with 2 in D1...

=SUMPRODUCT(MAX((A1:A100=D1)*(B1:B100+C1:C100)))

Change the upper row limits on each of the ranges from 100 to a row number
greater than (or equal to) the largest row you expect to have data in.

Rick

 

[cardan]

Thats it! Thank you! I was putting the MAX function on the outside of
the SUMPRODUCT, rather than within. Thanks again!