Peo Sjoblom Wrote:
That is the correct sumproduct formula but you need to change the
dynamic
range

payments_range_e

would be

=OFFSET(Payments!$E$2,0,0,COUNTA(Payments!$E$2:$E$ 65536),1)

since it starts in E2.

You could still use the counta($E:$E) but then you need offset by one
if you
have a header in E1
or else you will include that as well

=OFFSET(Payments!$E$2,0,0,COUNTA(Payments!$E:$E)-1,1)

assuming you have a header. Do the same for the other ranges

Personally I would probably use a large enough range in a defined name
without using a dynamic ranges since
if you by any chance would have a different count of items/values in
one of
the columns it would return a
#VALUE! error (array formulas like these need same sized ranges) and
it
might be hard to spot but if you know these ranges never will go
beyond
let's say 10000 cells you could define a name for
Payments!$A$2:$A$10000 and
so on, that way the ranges would always be equally sized and you
wouldn't
get an error

In theory, there shouldn't be a differing number of values across each
column, however, point taken.

I've changed the ranges to cover up to row 65536 instead of being
dynamic.

Thanks again! :)


--
willee
------------------------------------------------------------------------
willee's Profile: http://www.excelforum.com/member.php...o&userid=31189
View this thread: http://www.excelforum.com/showthread...hreadid=508589