I've tried this and it doesn't work either:

=IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data
Summary'!$B$2:$AD$2=B$22),--ISNUMBER('Director Data
Summary'!$B$19:$AD$19),('Director Data Summary'!$B$19:$AD$19)))


"Dave F" wrote:

Well, if you're looking to highlight NAs in that array, then maybe you could
conditionally format NAs to appear as 1s with a red background.

That way you can use the array in your sumproduct calculation AND show,
graphically, where the NAs are.

It seems to me that, as you have your formula now, SUMPRODUCT is applied to
that entire range. I don't know how you would tell SUMPRODUCT to look at
that whole range, and only use the values that are not NA.

Dave
--
Brevity is the soul of wit.


"Barb Reinhardt" wrote:

I want NA to be displayed in B19:AD19 if there isn't a result. I need the
sumproduct formula to be able to pull them out. I thought I could use
something like ISNUMBER() around the range that had the NA in them, but can't
seem to get that to work.

"Dave F" wrote:

What do you want to happen if there are N/A values? Return 0? One possible
solution would be to conditionally format the range B19:AD19 to show 0 where
there are N/A values. Or show 1 if you don't want the rest of your
calculations to be affected.

Dave
--
Brevity is the soul of wit.


"Barb Reinhardt" wrote:

I have the following equation:

=IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data
Summary'!$B$2:$AD$2=B$22),('Director Data Summary'!$B$19:$AD$19)))

It works if the last range doesn't have any N/A values in it. What do I
need to change to get it to ignore the N/A values?

Thanks