Defined Range Names in formulas

D

Darby

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

Luke M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top