Hi All, and thanks in advance
I am using the formula as follow and I wonder if there is any way to make it
work when the range is variable
=-SUMPRODUCT(--(N7:N1000<0),--(BA7:BA1000<"BD"),--(N7:N1000))

Any help really appreciate

Hi,

try 'Dynamic ranges'
(http://www.contextures.com/xlNames01.html#Dynamic) making sure the
ranges have the same length
or use a range like A1:A65535 which will cover an entire column in XL
versions before XL 2007


--
Pecoflyer

Cheers -
*_Membership_is_free_* & allows file upload -faster and better answers
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47109

Hi,
Thank you for your answer, I thought on that solution, what happens is that
I have an userform that each time a new project is entered it copy the data
in the summary sheet and then create a new tab for that project and copy the
information there as well, I was thinking that maybe I will be able to modify
that formula to include an N7:N range, or something like thta

"Pecoflyer" wrote:


Hi,

try 'Dynamic ranges'
(http://www.contextures.com/xlNames01.html#Dynamic) making sure the
ranges have the same length
or use a range like A1:A65535 which will cover an entire column in XL
versions before XL 2007


--
Pecoflyer

Cheers -
*_Membership_is_free_* & allows file upload -faster and better answers
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47109

Hi,

If there are no spaces before the last entry you can use

=-SUMPRODUCT(--(OFFSET(N7,0,0,COUNTA(N7:N5000))<0),--(OFFSET(BA7,0,0,COUNTA(BA7:BA5000))<"BD"),--(OFFSET(N7,0,0,COUNTA(N7:N5000))))

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Eduardo" wrote in message
...
Hi All, and thanks in advance
I am using the formula as follow and I wonder if there is any way to make
it
work when the range is variable
=-SUMPRODUCT(--(N7:N1000<0),--(BA7:BA1000<"BD"),--(N7:N1000))

Any help really appreciate

Hi Shane,
In the formula below you still is considering a # or rows i.e. N5000, what
if the rows go beyong that point

"Shane Devenshire" wrote:

Hi,

If there are no spaces before the last entry you can use

=-SUMPRODUCT(--(OFFSET(N7,0,0,COUNTA(N7:N5000))<0),--(OFFSET(BA7,0,0,COUNTA(BA7:BA5000))<"BD"),--(OFFSET(N7,0,0,COUNTA(N7:N5000))))

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Eduardo" wrote in message
...
Hi All, and thanks in advance
I am using the formula as follow and I wonder if there is any way to make
it
work when the range is variable
=-SUMPRODUCT(--(N7:N1000<0),--(BA7:BA1000<"BD"),--(N7:N1000))

Any help really appreciate