SUMPRODUCT with a #VALUE in a cell
 

In writing a formula, how will you skip the cell with a missing value, i.e.,
#VALUE?
Example below.
A B C
D
1 Date opened Date closed # of days opened Area of
responsibility
2 1/13/2006 1/31/2006 18
Case Line
3 1/17/2006 2/14/2006 28
Light Line
4 1/22/2006 - #VALUE!
Case Line
5 2/10/2006 2/28/2006 18
Case Line

I want to find the total # of days opened for Case Line.
Note: Column C is the difference of C and B which is also written in a
formula =SUM(B2-B1), etc. thus giving the value of column C4 #VALUE since B5
is still open.

SUMPRODUCT with a #VALUE in a cell
 

=SUMIF(Range,"<#VALUE!")


however it's better to avoid the text (-) at all or use an if formula to
return zero if not both cells have dates, then you can use a regular
SUM(Range)
Note that =SUM(B2-B1) is not necessary

=B2=B1

will suffice

Regards,

Peo Sjoblom

"FPJ" wrote:

In writing a formula, how will you skip the cell with a missing value, i.e.,
#VALUE?
Example below.
A B C
D
1 Date opened Date closed # of days opened Area of
responsibility
2 1/13/2006 1/31/2006 18
Case Line
3 1/17/2006 2/14/2006 28
Light Line
4 1/22/2006 - #VALUE!
Case Line
5 2/10/2006 2/28/2006 18
Case Line

I want to find the total # of days opened for Case Line.
Note: Column C is the difference of C and B which is also written in a
formula =SUM(B2-B1), etc. thus giving the value of column C4 #VALUE since B5
is still open.

SUMPRODUCT with a #VALUE in a cell
 

Apologize, I can'r see how you table look since on my end it looks like you
have
both dates and names in A? If not so try

=SUM(IF(A2:A20="case line",IF(ISNUMBER(C2:C20),C2:C20)))

entered with ctrl + shift & enter

adapt to fit your real ranges

Regards,

Peo Sjoblom

"Peo Sjoblom" wrote:

=SUMIF(Range,"<#VALUE!")


however it's better to avoid the text (-) at all or use an if formula to
return zero if not both cells have dates, then you can use a regular
SUM(Range)
Note that =SUM(B2-B1) is not necessary

=B2=B1

will suffice

Regards,

Peo Sjoblom

"FPJ" wrote:

In writing a formula, how will you skip the cell with a missing value, i.e.,
#VALUE?
Example below.
A B C
D
1 Date opened Date closed # of days opened Area of
responsibility
2 1/13/2006 1/31/2006 18
Case Line
3 1/17/2006 2/14/2006 28
Light Line
4 1/22/2006 - #VALUE!
Case Line
5 2/10/2006 2/28/2006 18
Case Line

I want to find the total # of days opened for Case Line.
Note: Column C is the difference of C and B which is also written in a
formula =SUM(B2-B1), etc. thus giving the value of column C4 #VALUE since B5
is still open.