Summing a Row

  • Thread starter Thread starter caradomski
  • Start date Start date
C

caradomski

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 )
 
I'm not sure with SUMPRODUCT, but some functions accept A:A (or $A:$A) to
select all column A.
 
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
 
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.

--
HTH

Bob Phillips

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