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

sorry the mestake I forgot the IF

=if(iserror(formula,0,formula))
hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Barb Reinhardt" escreveu:

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

Hi Barb,

Why not to remove the N/A values on the source?

use =iserror(formula,0,formula)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Barb Reinhardt" escreveu:

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

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

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

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

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

Hi Barb

Try the Array entered formula

{=IF(B24=0,NA(),
SUM(($B$1:$AD$1=$A37)*
('Director Data Summary'!$B$2:$AD$2=B$22)*
IF(ISNUMBER('Director Data Summary'!$B$19:$AD$19,
'Director Data Summary'!$B$19:$AD$19,0)))}

Use Ctrl+Shift+Enter (CSE) to commit or edit the formula instead of
Enter.
Do not type the curly braces { } yourself, Excel will enter them when
you use CSE


--
Regards

Roger Govier


"Barb Reinhardt" wrote in
message ...
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