Sumproduct doesn't work with columns... alternatives?

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?

You 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
="Sheet1A"&row(A9)&":A"&row(A9)+counta(A10:A$65536)-1

Of course indirect can be volatile if you insert or delete columns



"qwopzxnm" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> http://www.excelforum.com/member.php...o&userid=27557
> View this thread: http://www.excelforum.com/showthread...hreadid=513842
>

That should have been
="Sheet1A"&row(A9)&":A"&row(A9)+counta(A9:A$65536)-1


"Bob Tarburton" <reply2bob@_removethis_intergate.com> wrote in message
news:[email protected]...
> You 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
> ="Sheet1A"&row(A9)&":A"&row(A9)+counta(A10:A$65536)-1
>
> Of course indirect can be volatile if you insert or delete columns
>
>
>
> "qwopzxnm" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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:
>> http://www.excelforum.com/member.php...o&userid=27557
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=513842
>>
>
>

Bob Tarburton wrote...
>You 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
>="Sheet1A"&row(A9)&":A"&row(A9)+counta(A10:A$65536)-1
>
>Of course indirect can be volatile if you insert or delete columns
....

First, "Sheet1A9" 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

http://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




"qwopzxnm" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> http://www.excelforum.com/member.php...o&userid=27557
> View this thread: http://www.excelforum.com/showthread...hreadid=513842
>