Sumproduct formula not working with ranges

[Michelle]

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.

 

[Mike H]

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

 

[Roger Govier]

Hi Michelle

What happens if you change your ranges to Start at row 5 rather than row 1?
Your original formulae started at that row.

Also, you would better making Dynamic ranges rather than using 65000 rows
for everything.

Insert>Name>Define>
name lr
Refers to =COUNTA('DATA- Current QTR FY09'!$H:$H)

then
Name ClustFY09
Refers to =$H$5:INDEX($H:$H,lr)
Name ProdFY09
Refers to =$I$1:INDEX($I:$I,lr)
Name GrpFY09
Refers to =$J1:INDEX($J:$J,lr)
Name AmtFY09
Refers to =$K$1:INDEX($K:$K,lr)

Excel will automatically insert the sheet names for you provided you are on
the correct sheet when creating the names.
By setting lr (lastrow) based on just one column (the one where there is
always likely to be data), you are ensuring that all ranges will be of equal
length.

 

[Michelle]

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!

 

[Roger Govier]

Ooops

Sorry Michelle
All of those Refers to ranges should have started with $5 for the row.
I did it right for the first range, but force of habit made me use row 1 for
the rest.

 

[Dave Peterson]

You can only use whole columns in xl2007.

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!

 

[Michelle]

Sorry for not responding earlier, got pulled onto something else!

Okay so from Dave's reply, I know I can't use whole columns in the formula
as that only works in 2007, and I'm running 2003.

I'm now going back through all my ranges to make sure they have the "same"
range and that none of them has moved or changed, or are misaligned. I should
then be able to test if this is the problem, before implementing your Last
Row method.

Michelle