How do I find max then sum cells above & to the left?

[Iany]

If

A1 = 1 B1 = 2 C1 = 3
A2 = 4 B2 = 5 C2 = 6
A3 = 9 B3 = 8 C3 = 7

How do I find the maximum value in row 3 and then sum the numbers in rows 1
and 2 above and to the right of the maximum value in row 3? I have tried the
formula =SUM(OFFSET(MAX(A3:C3),-2,0,2,3)) but Excel doesn't appear to accept
the maximum function within the offset function.

Thanks in advance.
Ian

 

[yshridhar]

Try the following formula in A4, B4
=SUMIF(A$1:A$3,">"&MAX($C$1:$C$3),A$1:A$3)
with regards
Sridhar

 

[yshridhar]

sorry i couldnot understand the question.
My mistake
You can try it in the following way
In A4 = ADDRESS(3,MATCH(MAX(A3:C3),A3:C3,0))
a5 = =SUM(OFFSET(indirect(a4),-2,0,2,3))
with regards
Sridhar

 

[Max]

Perhaps more generic, placed in say, A5:
=SUM(OFFSET(IV1:IV2,,,,MATCH(MAX(3:3),3:3,0)-257))

 

[RagDyer]

Try this:

=SUM(INDEX(A1:C1,MATCH(MAX(A3:C3),A3:C3,0)+1):INDEX(A2:C2,MATCH(MAX(A3:C3),A3:C3,0)+1))

You must realize though, that if the value 9 (highest) was in C3 instead of
A3, you'd get referenced to a cell outside your posted range, and that would
return a #REF! error.

 

[Ron Coderre]

Perhaps this:

=SUMPRODUCT((COLUMN(A1:C2)>=MATCH(MAX(A3:C3),A3:C3,0))*A1:C2)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)