SUMPRODUCT With External Range Name?
 

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 A
$A$2:$A$26496 = DATERANGE
$D$2:$D$26496 = VOLUME_ACT

HELP!!!

Thanks,
Ray

SUMPRODUCT With External Range Name?
 

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" wrote in
message ...
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 A
$A$2:$A$26496 = DATERANGE
$D$2:$D$26496 = VOLUME_ACT

HELP!!!

Thanks,
Ray

SUMPRODUCT With External Range Name?
 

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



"Peo Sjoblom" wrote:

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" wrote in
message ...
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 A
$A$2:$A$26496 = DATERANGE
$D$2:$D$26496 = VOLUME_ACT

HELP!!!

Thanks,
Ray

SUMPRODUCT With External Range Name?
 

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 insertnamedefine 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!VOLU ME_ACT
)


that also worked for me

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




--


Regards,


Peo Sjoblom




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



"Peo Sjoblom" wrote:

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" wrote in
message ...
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 A
$A$2:$A$26496 = DATERANGE
$D$2:$D$26496 = VOLUME_ACT

HELP!!!

Thanks,
Ray