Using NAMES in SUMPRODUCT giving error

[SFC Traver]

I had just posted a question that was answered, so I tried to make it easier
to understand the formula for whomever takes over for me and defined the
columns as REDCAT, Gender, and Grade doing Insert>Name>Define.

The formula works fine like this:
=SUMPRODUCT(--('By-Name Population'!D24000="WHITE"),--('By-Name
Population'!E2:E4000="M"),--('By-Name Population'!F2:F4000="Jr NCO"))

But when I tried to replace the cell ranges with the names I defined:
=SUMPRODUCT(--(REDCAT="WHITE"),--(Gender="M"),--(Grade="Jr NCO"))

I get a #NUM error. Am I not using the Name correctly? I've even tried when
I'm using the formula to Insert>Name>Paste and it doesn't work. Is it because
the data is on another worksheet? I really need it to look at the entire
column because it could expand.

Thanks for the help!

 

[Dave Peterson]

If you're using xl2007, then you can use the entire column.

If you're using xl2003 or below, then you can't.

Maybe you can use all the rows but one.

or maybe you can use a dynamic range that changes with the amount of data.

Debra Dalgleish explains dynamic range names here:
http://contextures.com/xlNames01.html#Dynamic

 

[Stephen Lloyd]

I hope this will help...
I don't think SUMPRODUCT can reference whole columns. A solution to the
problem regarding expansion would be to use dynamically named ranges which
will expand whenever a user puts a value into the next empty cell in a range.

You can enter them by going to Insert>Name>Define and entering the formulas
below. You will need to handle text and number entries differently so I'll
provide both formulas and reference a website.

For number values
=OFFSET(Sheet1!$H$2,0,0,MATCH(1E+306,Sheet1!$H:$H,1),1)
for text values
=OFFSET(Sheet1!$C$2,0,0,MATCH("*",Sheet1!$C:$C,-1),1)

These examples assume a header row so without a header row you would change
$C$2 to $C$1, etc.

OzGrid has an excellent explanation with plenty of examples at this URL
http://www.ozgrid.com/Excel/advanced-dynamic-ranges.htm

Thanks for your service, Sergeant

Stephen