Is there a way to get rid of "######"
 

A6 contains this formula:
=SUMPRODUCT(--(A1:A58/24),A1:A5-8/24)*(SUM(A1:A5)*24<40)

This formula is doing what I want however if I enter a word into one of the
data cells instead of a time, the cell with the formula says ######. Is there
a way to have the formula only recognize a time entry and ignore a word entry
and still do the calculations I need.
--
Lee D.

Is there a way to get rid of "######"
 

Try:

=SUMPRODUCT(--(ISNUMBER(A1:A58/24)),ISNUMBER((A1:A5-8/24))*(SUM(A1:A5)*24<40))

"Lee" wrote:

A6 contains this formula:
=SUMPRODUCT(--(A1:A58/24),A1:A5-8/24)*(SUM(A1:A5)*24<40)

This formula is doing what I want however if I enter a word into one of the
data cells instead of a time, the cell with the formula says ######. Is there
a way to have the formula only recognize a time entry and ignore a word entry
and still do the calculations I need.
--
Lee D.

Is there a way to get rid of "######"
 

Test the cell for data type/and formatting if your version of Excel allows.
--
Sincerely,
Mike Leonard
GE Reaves Engineering, Inc.
San Antonio, Texas 78247


"Lee" wrote:

A6 contains this formula:
=SUMPRODUCT(--(A1:A58/24),A1:A5-8/24)*(SUM(A1:A5)*24<40)

This formula is doing what I want however if I enter a word into one of the
data cells instead of a time, the cell with the formula says ######. Is there
a way to have the formula only recognize a time entry and ignore a word entry
and still do the calculations I need.
--
Lee D.

Is there a way to get rid of "######"
 

Toppers,
That formula is ignoring both words and time entry in the before mentioned
cells.
Any more suggestions?
--
Lee D.


"Toppers" wrote:

Try:

=SUMPRODUCT(--(ISNUMBER(A1:A58/24)),ISNUMBER((A1:A5-8/24))*(SUM(A1:A5)*24<40))

"Lee" wrote:

A6 contains this formula:
=SUMPRODUCT(--(A1:A58/24),A1:A5-8/24)*(SUM(A1:A5)*24<40)

This formula is doing what I want however if I enter a word into one of the
data cells instead of a time, the cell with the formula says ######. Is there
a way to have the formula only recognize a time entry and ignore a word entry
and still do the calculations I need.
--
Lee D.