software

Hello-

Is there a way to set up a SUMPRODUCT so that it will automatically
adjust the array length to the last value in a column???

For example if I have column A data that starts in row 9 and ends in
row 50. Part of my SUMPRODUCT would look like --(A9-A50),

Then if I add 5 rows of data could I set up a SUMPRODUCT to
automatically adjust to now include those extra 5 rows?--
qwopzxnm
------------------------------------------------------------------------
qwopzxnm's Profile: www.excelforum.com/member.php...oamp;userid=27557
View this thread: www.excelforum.com/showthread...hreadid=513842You could use indirect ranges such as
=SUMPRODUCT(--(indirect($A$1)=condition1),--(indirect($B$1)=condition2))
Where A1 and B1 name the ranges with formula such as
=quot;Sheet1Aquot;amp;row(A9)amp;quot;:Aquot;amp;row(A9) counta(A10:A$65536 )-1

Of course indirect can be volatile if you insert or delete columns
quot;qwopzxnmquot; gt; wrote in
message ...
gt;
gt; Hello-
gt;
gt; Is there a way to set up a SUMPRODUCT so that it will automatically
gt; adjust the array length to the last value in a column???
gt;
gt; For example if I have column A data that starts in row 9 and ends in
gt; row 50. Part of my SUMPRODUCT would look like --(A9-A50),
gt;
gt; Then if I add 5 rows of data could I set up a SUMPRODUCT to
gt; automatically adjust to now include those extra 5 rows?
gt;
gt;
gt; --
gt; qwopzxnm
gt; ------------------------------------------------------------------------
gt; qwopzxnm's Profile:
gt; www.excelforum.com/member.php...oamp;userid=27557
gt; View this thread: www.excelforum.com/showthread...hreadid=513842
gt;
That should have been
=quot;Sheet1Aquot;amp;row(A9)amp;quot;:Aquot;amp;row(A9) counta(A9:A$655 36)-1quot;Bob Tarburtonquot; gt; wrote in message
...
gt; You could use indirect ranges such as
gt; =SUMPRODUCT(--(indirect($A$1)=condition1),--(indirect($B$1)=condition2))
gt; Where A1 and B1 name the ranges with formula such as
gt; =quot;Sheet1Aquot;amp;row(A9)amp;quot;:Aquot;amp;row(A9) counta(A10:A$65536 )-1
gt;
gt; Of course indirect can be volatile if you insert or delete columns
gt;
gt;
gt;
gt; quot;qwopzxnmquot; gt; wrote
gt; in message ...
gt;gt;
gt;gt; Hello-
gt;gt;
gt;gt; Is there a way to set up a SUMPRODUCT so that it will automatically
gt;gt; adjust the array length to the last value in a column???
gt;gt;
gt;gt; For example if I have column A data that starts in row 9 and ends in
gt;gt; row 50. Part of my SUMPRODUCT would look like --(A9-A50),
gt;gt;
gt;gt; Then if I add 5 rows of data could I set up a SUMPRODUCT to
gt;gt; automatically adjust to now include those extra 5 rows?
gt;gt;
gt;gt;
gt;gt; --
gt;gt; qwopzxnm
gt;gt; ------------------------------------------------------------------------
gt;gt; qwopzxnm's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=27557
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=513842
gt;gt;
gt;
gt;
Bob Tarburton wrote...
gt;You could use indirect ranges such as
gt;=SUMPRODUCT(--(indirect($A$1)=condition1),--(indirect($B$1)=condition2))
gt;Where A1 and B1 name the ranges with formula such as
gt;=quot;Sheet1Aquot;amp;row(A9)amp;quot;:Aquot;amp;row(A9) counta(A10:A$6553 6)-1
gt;
gt;Of course indirect can be volatile if you insert or delete columns
....

First, quot;Sheet1A9quot; is an invalid textref. The exclamation point between
the worksheet name and the column letter isn't optional.

INDIRECT is *always* 'volatile'. Perhaps you mean dangerous or fragile?
INDIRECT is also not the best choice. OFFSET would be if volatile
functions are acceptable.

=SUMPRODUCT(--(OFFSET(A9,0,0,COUNTA(A9:A65536),1)=condition1),.. .)

However, this depends on there being no gaps, blank cells, in column A
before the final nonblank value.

Safer and nonvolatile to use

=SUMPRODUCT(--(A9:INDEX(A:A,LOOKUP(2,1/(1-ISBLANK(A9:A65536)),
ROW(A9:A65536)))=condition1),...)There are several ways to do this, one way without using a volatile function
would be

=SUMPRODUCT(--($A$9:INDEX($A$9:$A$20000,COUNTA($A$9:$A$20000))))

which would be the same as

=SUMPRODUCT(--($A$9:$A$50))

now add 5 values

and it will adapt, you can also define a dynamic named range like here

www.contextures.com/xlNames01.html#Dynamic

that particular website is the best IMHO if you want to learn excel

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

quot;qwopzxnmquot; gt; wrote in
message ...
gt;
gt; Hello-
gt;
gt; Is there a way to set up a SUMPRODUCT so that it will automatically
gt; adjust the array length to the last value in a column???
gt;
gt; For example if I have column A data that starts in row 9 and ends in
gt; row 50. Part of my SUMPRODUCT would look like --(A9-A50),
gt;
gt; Then if I add 5 rows of data could I set up a SUMPRODUCT to
gt; automatically adjust to now include those extra 5 rows?
gt;
gt;
gt; --
gt; qwopzxnm
gt; ------------------------------------------------------------------------
gt; qwopzxnm's Profile:
gt; www.excelforum.com/member.php...oamp;userid=27557
gt; View this thread: www.excelforum.com/showthread...hreadid=513842
gt;