DIFICULT SUM + SUM.IF DATE RANGE CRITERIA

B

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top