SUMPRODUCT question
 

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

SUMPRODUCT question
 

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

SUMPRODUCT question
 

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

SUMPRODUCT question
 

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

SUMPRODUCT question
 

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

SUMPRODUCT question
 

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

SUMPRODUCT question
 

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

SUMPRODUCT question
 

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

SUMPRODUCT question
 

I actually ended up using this array entered formula last week

=IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--($B$2:$AD$2=C$22),(IF(ISNA($B$19:$AD$19)=FALSE,$B$ 19:$AD$19))))

I still don't understand why I couldn't use this
--(ISNUMBER($B$19:$AD$19)) as part of my array formula. It just never
worked. Any ideas?

Thanks

"Roger Govier" wrote:

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

SUMPRODUCT question
 

Hi Barb

My solution used IF(ISNUMBER()) which produces the same result as
IF(NOT(ISNA())) or IF(ISNA()=FALSE)

It is the use of the IF() clause that is important. That is saying only
use the value from B19:AD19 IF the value is a number or is not #N/A.

Without the IF clause, as you were trying, --ISNUMBER() operating on a
range containing numbers and #N/A's value was returning an array of 1's
and 0's, but then multiplying the result by the same range give a #N/A
result as 0 * #N/A will be #N/A thereby invalidating the whole result.

The IF clause is causing only the values that are not #N/A to be used in
the remainder of the calculation.

--
Regards

Roger Govier


"Barb Reinhardt" wrote in
message ...
I actually ended up using this array entered formula last week

=IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--($B$2:$AD$2=C$22),(IF(ISNA($B$19:$AD$19)=FALSE,$B$ 19:$AD$19))))

I still don't understand why I couldn't use this
--(ISNUMBER($B$19:$AD$19)) as part of my array formula. It just
never
worked. Any ideas?

Thanks

"Roger Govier" wrote:

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