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