SUMPRODUCT With External Range Name?

[RayportingMonkey]

I am able to pull through data with the formula posted below, however the
ranges need to be dynamic since the range is expanded daily. The ranges are
setup with range names. However, when I attempt to change the hard coded cell
references to range names, the formula retuens a #VALUE error.

=SUMPRODUCT(--(YEAR([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=2007),--(MONTH([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=10),[LOB_ALT.xls]RAW_DATA!$D$2:$D$26496)

The Range Names Are:
$A$2:$A$26496 = DATERANGE
$D$2:$D$26496 = VOLUME_ACT

HELP!!!

Thanks,
Ray

 

[Peo Sjoblom]

You have text in your data, month and year return value errors if there is a
single text value in the range you are checking


--


Regards,


Peo Sjoblom

 

[RayportingMonkey]

Chocolate in my Peanutbutter maybe... But not text in my data.

Actually, to be honest, the last cell in the range is a text cell. However,
to test I changed the named range to exclude that cell and I get the same
result.

Again, with hard coded cell references, i.e.
[LOB_ALT.xls]RAW_DATA!$A$2:$A$26496 I get the desired result.

When I change the absolute reference to a range name the formula bombs...

Working under a deadline - any additional help, ideas, alternate solutions
are greatly apprecaited!

Thanks,
Ray

You have text in your data, month and year return value errors if there is a
single text value in the range you are checking


--


Regards,


Peo Sjoblom

I am able to pull through data with the formula posted below, however the
ranges need to be dynamic since the range is expanded daily. The ranges
are
setup with range names. However, when I attempt to change the hard coded
cell
references to range names, the formula retuens a #VALUE error.

=SUMPRODUCT(--(YEAR([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=2007),--(MONTH([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=10),[LOB_ALT.xls]RAW_DATA!$D$2:$D$26496)

The Range Names Are:
$A$2:$A$26496 = DATERANGE
$D$2:$D$26496 = VOLUME_ACT

HELP!!!

Thanks,
Ray

 

[Peo Sjoblom]

Sorry didn't catch that you were using names, how do you define your name
and where do you define your name. If you are using a formula in one
workbook and referring to another workbook you can do insert>name>define in
the workbook that holds the formula

=[LOB_ALT.xls]RAW_DATA!$A$2:$A$26496

for DATERANGE

and do the same for VOLUME_ACT and include the workbook as well

then this formula works for me

=SUMPRODUCT(--(YEAR(DATERANGE)=2007),--(MONTH(DATERANGE)=10),VOLUME_ACT )


or use the named range name in the source workbook and use this formula


=SUMPRODUCT(--(YEAR(LOB_ALT.xls!DATERANGE)=2007),--(MONTH(LOB_ALT.xls!DATERANGE)=10),LOB_ALT.xls!VOLUME_ACT
)


that also worked for me

as you can see you don't need the brackets around the workbook name




--


Regards,


Peo Sjoblom

Chocolate in my Peanutbutter maybe... But not text in my data.

Actually, to be honest, the last cell in the range is a text cell.
However,
to test I changed the named range to exclude that cell and I get the same
result.

Again, with hard coded cell references, i.e.
[LOB_ALT.xls]RAW_DATA!$A$2:$A$26496 I get the desired result.

When I change the absolute reference to a range name the formula bombs...

Working under a deadline - any additional help, ideas, alternate solutions
are greatly apprecaited!

Thanks,
Ray

You have text in your data, month and year return value errors if there
is a
single text value in the range you are checking


--


Regards,


Peo Sjoblom

I am able to pull through data with the formula posted below, however
the
ranges need to be dynamic since the range is expanded daily. The ranges
are
setup with range names. However, when I attempt to change the hard
coded
cell
references to range names, the formula retuens a #VALUE error.

=SUMPRODUCT(--(YEAR([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=2007),--(MONTH([LOB_ALT.xls]RAW_DATA!$A$2:$A$26496)=10),[LOB_ALT.xls]RAW_DATA!$D$2:$D$26496)

The Range Names Are:
$A$2:$A$26496 = DATERANGE
$D$2:$D$26496 = VOLUME_ACT

HELP!!!

Thanks,
Ray