SumIf

[Shams]

Folks,
I am looking at the following data:

Total Value Months
$1,000 2
$1,500 3
$1,200 4
$1,350 5
$1,500 6
$2,200 5
$1,700 7
$950 5


Basically, I need to be able to subtotal anything between 4 months < Months
to Expiry < 7 months

So, in this case, be able to sum up Total value for the 5 months...i need
this to be a flexible formula within a cell that is able to look for this
criteria in another worksheet (containing the data). So, how do I do this..i
am not able to use Sumif (this would have been great since I have many rows
of data to evaluate)...I am able to do an IF statement but this is only
relevant for one row..how can i encapsulate the 5 months from diff. rows of
evaluation into one cell of a formula..

is this possible or do i just need to do a sumif in the worksheet file with
the "5 months" criteria...

Hope this make sense!! Thanks in advance for your help.

Cheers,
Shams.

 

[Shams]

I found a solution that seems to be working...since my stipulation is expiry
being between 4 and 6 months..i basically did 2 sumifs subtracting "<= 4
months" from "< 6 months"

but if there is a more elegant solution then that would be golden!!

 

[JBoyer]

Sorry I can't help, but the reason I am writing is I have a similar problem,
I was wondering if you could explain your "longer way" since I don't know any
other way and it could be the only way. For my worksheet though, I want it to
add all the cells that have a corresponding cell that is not blank, instead
of being between 4 and 7.

1 5
2 -
3 6
4 -

So for instance, in this example i would only want to add the 1 and 3,
because the 2 and 4 have blank values.

Hope you can help, Thanks

 

[Shams]

Can't you just do a Sumif(B:B,"<>0",A:A)..if you meant - if any of the rows
in Column B is not equal to zero then sum if Column A - that would give you
1+3 = 4

it gave me 11

 

[Bob Phillips]

FYI, the way referred to would be something like

=SUMIF(A:A,">7",B:B)-SUMIF(A:A,">10",B:B)

would sum all values in B where the value in A is between 8 and 10.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)