Dynamic multiple-condition array function with more than one name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)*(nmeBacklogRowTypeColumn="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.
 
Are all the dynamic ranges the same size?

Biff

Gary F said:
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.
Click to expand...
 
Back
Top