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.

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!!

"Shams" wrote:

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.

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" wrote:

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!!

"Shams" wrote:

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.

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
"JBoyer" wrote:

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" wrote:

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!!

"Shams" wrote:

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.

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)

"JBoyer" wrote in message
...
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" wrote:

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!!

"Shams" wrote:

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.