Sumproduct formula not working with ranges
 

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.

Sumproduct formula not working with ranges
 

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.

Sumproduct formula not working with ranges
 

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.

InsertNameDefine
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.

--
Regards
Roger Govier

"Michelle" wrote in message
...
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.

Sumproduct formula not working with ranges
 

Mike H;216777 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.


Your first formula has 4 ranges and the second one only 3. Have you
named a range (ClustFY09) containing two columns (I and J)? I don't
think SUMPRODUCT likes this


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=59540

Sumproduct formula not working with ranges
 

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.

Sumproduct formula not working with ranges
 

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.

--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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.

InsertNameDefine
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.

--
Regards
Roger Govier

"Michelle" wrote in message
...
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.

Sumproduct formula not working with ranges
 

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

Sumproduct formula not working with ranges
 

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


"Roger Govier" wrote:

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.

--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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.

InsertNameDefine
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.

--
Regards
Roger Govier

"Michelle" wrote in message
...
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.