Summing a Row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

caradomski@hotmail.com - 18 Sep 2006 20:20 GMT

I would like to use enter row in my formula, instead of spelling out
A4:A2500 in my sumproduct formula. Example...

SUMPRODUCT(--(MONTH(Closed!$A$4:$A$10000)<(MONTH($D$2)+1)),--(LEFT((Closed!$C$4:$C$10000),(MONTH($D$1)-1))=F$7),Closed!$H$4:$H$10000,--((Closed!$AP$4:$AP$10000)=$B9))

Instead of using (Closed!$A$4:$A$10000) is there away to all of Column
A or at least $A$4:$A( all )

Reply to this Message

Ian - 18 Sep 2006 20:45 GMT

I'm not sure with SUMPRODUCT, but some functions accept A:A (or $A:$A) to
select all column A.

Signature

Ian
--

>I would like to use enter row in my formula, instead of spelling out
> A4:A2500 in my sumproduct formula. Example...
[quoted text clipped - 3 lines]
> Instead of using (Closed!$A$4:$A$10000) is there away to all of Column
> A or at least $A$4:$A( all )

Reply to this Message

Trevor Shuttleworth - 18 Sep 2006 21:23 GMT

Not in SUMPRODUCT. This needs a range similar to that in your formula

I think the only way around this would be to create variable named ranges
for each of the columns. But that can be difficult unless there are no gaps
in the data.

Regards

Trevor

Reply to this Message

Bob Phillips - 19 Sep 2006 08:37 GMT

SP must have a defined range, but you can use dynamic ranges, so instead of

Closed!$A$4:$A$10000

you could use

OFFSET(Closed!$A$4,,,COUNTA(Closed!$A$4:$A$65536)

You would use the same counter in other parts, but with a different base
cell, so

Closed!$C$4:$C$10000

would be

OFFSET(Closed!$C$4,,,COUNTA(Closed!$A$4:$A$65536)

Better would be to create named ranges with a RefersTo value of that dynamic
value, and use those in the formulae.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I would like to use enter row in my formula, instead of spelling out
> A4:A2500 in my sumproduct formula. Example...

SUMPRODUCT(--(MONTH(Closed!$A$4:$A$10000)<(MONTH($D$2)+1)),--(LEFT((Closed!$
C$4:$C$10000),(MONTH($D$1)-1))=F$7),Closed!$H$4:$H$10000,--((Closed!$AP$4:$A
P$10000)=$B9))

> Instead of using (Closed!$A$4:$A$10000) is there away to all of Column
> A or at least $A$4:$A( all )

Reply to this Message