DIFICULT SUM + SUM.IF DATE RANGE CRITERIA

[Bruno]

Hi guys i need to do a weird sum.
Here's the data:

0 x
-2 x
-1 x
3 x
-4 x
1 max
5 x
8 x
9 x
1 x

I need to sum the 1st column from the bottom, to the same line of the max on
the right column.
First thing i remembered =sum(xx:max(xx:xx)) but it doesn't work, i think
because sum() does't allow any operations in its arguments.
Second was to store the line of the max and try to join the column and then
sum them but =lin(max(xx:xx)) doesn't work either
Third i tried =sum.if() but i don't have much experience with this function.

Any ideas?




Also i'm trying to perfect a SUM.IF with a DATE RANGE CRITERIA
instead of:
=SUM.IF(I4:I25,">=2009/1/1",R4:R25)-SUM.IF(I4:I25,">=2010/1/1",R4:R25)

why can't i use?
=sum.if(xx:xx,year(xx:xx)=2009,yx:yx)
or
=sum.if(xx:xx,year(xx:xx)="2009",yx:yx)

Any ideas?


Thanks for any help

 

[Bob Phillips]

=SUM(A:A)-SUM(A1:INDEX(A:A,MATCH("max",B:B,0)-1))

 

[Shane Devenshire]

Hi,

Try something like this:

=SUM(OFFSET(A1,MATCH("max",B:B,)-1,,2^9))

 

[Bruno]

Thanks for the help knowing new funtions. Case solved with

=SUM(A10:INDEX(A1:B10,MATCH(MAX(B1:B10),B1:B10,0),1))

A lot simpler than I thought