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

  • Thread starter Thread starter qwopzxnm
  • Start date Start date
Q

qwopzxnm

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
 
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
Click to expand...
....

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
 
Back
Top