Defined Range Names in formulas
 

I am using Excel 2007 and am trying to reference defined name ranges in a
sumproduct formula. When I use the names the formula returns #N/A but when I
replace the names with their ranges, the formula works fine. The formula
that works is:

=SUMPRODUCT(('Billing'!C2:C1000=C2)*('Billing'!B2: B1000=C1)*'Billing'!AK2:AK1000)

The range 'Billing'!AK2:AK1000 is defined as SF_Rev. When I substitue
'Billing'!AK2:AK1269 with SF_Rev the formula fails. That formula is:

=SUMPRODUCT(('Billing'!C2:C1000=C2)*('Billing'!B2: B1000=C1)*(SF_Rev))

Any thought would be appreciated.

Defined Range Names in formulas
 

Check that SF_Rev is defined as ='Billing'!$AK$2:$AK$1000 (absolute) rather
than ='Billing'!AK2:AK1000 (relative).

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Darby" wrote in message
...
I am using Excel 2007 and am trying to reference defined name ranges in a
sumproduct formula. When I use the names the formula returns #N/A but
when I
replace the names with their ranges, the formula works fine. The formula
that works is:

=SUMPRODUCT(('Billing'!C2:C1000=C2)*('Billing'!B2: B1000=C1)*'Billing'!AK2:AK1000)

The range 'Billing'!AK2:AK1000 is defined as SF_Rev. When I substitue
'Billing'!AK2:AK1269 with SF_Rev the formula fails. That formula is:

=SUMPRODUCT(('Billing'!C2:C1000=C2)*('Billing'!B2: B1000=C1)*(SF_Rev))

Any thought would be appreciated.

Defined Range Names in formulas
 

Is SF_Rev defined as:
'Billing'!AK2:AK1000
or
'Billing'!$AK$2:$AK$1000

If the former, the placement of the formula itself would change what area
the name actually refers to, leading to problems.

the other thing to check would be that all the cells in AK2:Ak1000 actually
contain values, and not errors.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Darby" wrote:

I am using Excel 2007 and am trying to reference defined name ranges in a
sumproduct formula. When I use the names the formula returns #N/A but when I
replace the names with their ranges, the formula works fine. The formula
that works is:

=SUMPRODUCT(('Billing'!C2:C1000=C2)*('Billing'!B2: B1000=C1)*'Billing'!AK2:AK1000)

The range 'Billing'!AK2:AK1000 is defined as SF_Rev. When I substitue
'Billing'!AK2:AK1269 with SF_Rev the formula fails. That formula is:

=SUMPRODUCT(('Billing'!C2:C1000=C2)*('Billing'!B2: B1000=C1)*(SF_Rev))

Any thought would be appreciated.