Here's something weird I can't figure out. The problem is that I need a cell
to look at 1 dynamic range where the values in those ranges match
dynamic/static values. The dynamic thing eliminates the D* functions (which
seem to need static values like "Hat" rather than "A1").

So, if I have two named ranges
nmeBacklogProjectColumn which is really $A$2:$A$2000
nmeBacklogProjectColumn which is really $Z$2:$Z$2000

and I do
{=SUM((nmeBacklogProjectColumn=A11)*(nmeBacklogRow TypeColumn="Task")*1)}

I get "#N/A"

but if I do any of the following
{=SUM((nmeBacklogProjectColumn=A11)*1)}
{=SUM((nmeBacklogRowTypeColumn="Task")*1)}

I get valid results and if I do this

{=SUM(($A$2:$A$2000=A11)*($Z$2:$Z$2000="Task")*1)}

I get the result I wanted originally

However, the $2000 number is arbitrary - it's growing dynamically so I'd
rather use a named range so I can change it one place and the sheet is
updated.

I think I figured this out. My problem is that the range's are dynamic, a la

=OFFSET(WorkBacklog!$A$2,0,0,COUNTA(WorkBacklog!$A :$A),1)

if I change them to static, they work fine.

However, the above works okay in other situations.. Wierd.

"Gary F" wrote:

Here's something weird I can't figure out. The problem is that I need a cell
to look at 1 dynamic range where the values in those ranges match
dynamic/static values. The dynamic thing eliminates the D* functions (which
seem to need static values like "Hat" rather than "A1").

So, if I have two named ranges
nmeBacklogProjectColumn which is really $A$2:$A$2000
nmeBacklogProjectColumn which is really $Z$2:$Z$2000

and I do
{=SUM((nmeBacklogProjectColumn=A11)*(nmeBacklogRow TypeColumn="Task")*1)}

I get "#N/A"

but if I do any of the following
{=SUM((nmeBacklogProjectColumn=A11)*1)}
{=SUM((nmeBacklogRowTypeColumn="Task")*1)}

I get valid results and if I do this

{=SUM(($A$2:$A$2000=A11)*($Z$2:$Z$2000="Task")*1)}

I get the result I wanted originally

However, the $2000 number is arbitrary - it's growing dynamically so I'd
rather use a named range so I can change it one place and the sheet is
updated.

Are all the dynamic ranges the same size?

Biff

"Gary F" wrote in message
...
I think I figured this out. My problem is that the range's are dynamic, a
la

=OFFSET(WorkBacklog!$A$2,0,0,COUNTA(WorkBacklog!$A :$A),1)

if I change them to static, they work fine.

However, the above works okay in other situations.. Wierd.

"Gary F" wrote:

Here's something weird I can't figure out. The problem is that I need a
cell
to look at 1 dynamic range where the values in those ranges match
dynamic/static values. The dynamic thing eliminates the D* functions
(which
seem to need static values like "Hat" rather than "A1").

So, if I have two named ranges
nmeBacklogProjectColumn which is really $A$2:$A$2000
nmeBacklogProjectColumn which is really $Z$2:$Z$2000

and I do
{=SUM((nmeBacklogProjectColumn=A11)*(nmeBacklogRow TypeColumn="Task")*1)}

I get "#N/A"

but if I do any of the following
{=SUM((nmeBacklogProjectColumn=A11)*1)}
{=SUM((nmeBacklogRowTypeColumn="Task")*1)}

I get valid results and if I do this

{=SUM(($A$2:$A$2000=A11)*($Z$2:$Z$2000="Task")*1)}

I get the result I wanted originally

However, the $2000 number is arbitrary - it's growing dynamically so I'd
rather use a named range so I can change it one place and the sheet is
updated.

Yep.

"Biff" wrote:

Are all the dynamic ranges the same size?

Biff

"Gary F" wrote in message
...
I think I figured this out. My problem is that the range's are dynamic, a
la

=OFFSET(WorkBacklog!$A$2,0,0,COUNTA(WorkBacklog!$A :$A),1)

if I change them to static, they work fine.

However, the above works okay in other situations.. Wierd.

"Gary F" wrote:

Here's something weird I can't figure out. The problem is that I need a
cell
to look at 1 dynamic range where the values in those ranges match
dynamic/static values. The dynamic thing eliminates the D* functions
(which
seem to need static values like "Hat" rather than "A1").

So, if I have two named ranges
nmeBacklogProjectColumn which is really $A$2:$A$2000
nmeBacklogProjectColumn which is really $Z$2:$Z$2000

and I do
{=SUM((nmeBacklogProjectColumn=A11)*(nmeBacklogRow TypeColumn="Task")*1)}

I get "#N/A"

but if I do any of the following
{=SUM((nmeBacklogProjectColumn=A11)*1)}
{=SUM((nmeBacklogRowTypeColumn="Task")*1)}

I get valid results and if I do this

{=SUM(($A$2:$A$2000=A11)*($Z$2:$Z$2000="Task")*1)}

I get the result I wanted originally

However, the $2000 number is arbitrary - it's growing dynamically so I'd
rather use a named range so I can change it one place and the sheet is
updated.