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?


--
qwopzxnm
------------------------------------------------------------------------
qwopzxnm's Profile: http://www.excelforum.com/member.php...o&userid=27557
View this thread: http://www.excelforum.com/showthread...hreadid=513842

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

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

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

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

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


"Bob Tarburton" wrote in message
...
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" wrote
in message ...

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

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

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$6553 6)-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),...)

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

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

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