SUM after using MAX

[y_not]

Hi,

My sheet records data on a monthly basis. I use MAX to find the highest number (which can be in any of the four weeks of the month). I then need to SUM numbers in the row below.

For example cells A1:A4 contain the following: 20;31;70;64
Cells B1:B4contain the following: 2;4;1;6

The MAX function will select 70 I then need to to SUM the 2;4;1

I hope that this makes sense - can you help please?

Many thanks

Tony

[Toppers]

Assuming you always sum from row 1 to the row having the maximum value:

=SUM(OFFSET($B$1,0,0,MATCH(MAX($A$1:$A$4),$A$1:$A$4,0),1))


If this is not the rule, then come back with a (clearer) explanation.

HTH


"y_not" wrote:

>
> Hi,
>
> My sheet records data on a monthly basis. I use MAX to find the highest
> number (which can be in any of the four weeks of the month). I then need
> to SUM numbers in the row below.
>
> For example cells A1:A4 contain the following: 20;31;70;64
> Cells B1:B4contain the following: 2;4;1;6
>
> The MAX function will select 70 I then need to to SUM the 2;4;1
>
> I hope that this makes sense - can you help please?
>
> Many thanks
>
> Tony
>
>
> --
> y_not
> ------------------------------------------------------------------------
> y_not's Profile: http://www.excelforum.com/member.php...o&userid=19947
> View this thread: http://www.excelforum.com/showthread...hreadid=565625
>
>

[y_not]

You are, without doubt a genius!!

From a really crap explaination you have managed to come up with a working solution!!!

Thanks Mate