sumproduct using variable range

[Eduardo]

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

 

[Pecoflyer]

Hi,

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

 

[Eduardo]

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

 

[Shane Devenshire]

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]

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