You can only use whole columns in xl2007.

Michelle wrote:

Hi Mike would it make a difference if i made the range the whole column eg H:H

Sorry if this appears multiple times. having a few pc issues!

"Mike H" wrote:

Michelle,

There's nothing wrong with the formula and try as I might I can't make it
produce a NUM error. A VALUE error would be produced if any of these named
ranges were of unequal size.

Can we see a sample of your data.

Mike

"Michelle" wrote:

Hi,

I applied a Sumproduct formula to a complex formula, that worked fine while
I was using the formula to go and pick up the data:
=(SUMPRODUCT(('DATA- Current QTR FY09'!H5:H11121='test sheet'!B18)*('DATA-
Current QTR FY09'!I5:I11121='test sheet'!C18)*('DATA- Current QTR
FY09'!J5:J11121='test sheet'!D18),'DATA- Current QTR FY09'!K5:K11121))

But when i tried to make the formula "easier" to read and maintian by
renaming the selected columns with range names, the formula now returns a
NUM# error.

=(SUMPRODUCT((ClustFY09='test sheet'!B21)*(ProdFY09='test
sheet'!C21)*(GrpFY09='test sheet'!D21), AmtFY09))

Does anyone know what the problem is? The ranges are the header down to row
65000. I also tried to name the whole column but that didn't work either.


--

Dave Peterson