How do I 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.
=SUMPRODUCT(--(ISNUMBER(A1:A58/24)),ISNUMBER((A1:A5-8/24))*(SUM(A1:A5)*24<40))
did not work. This formula ignores both word and time entries when I applied
it.


--
Lee D.

--
Lee D.

How do I get rid of "#######"
 

Perhaps not a good way of doing it but:

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

Array entered with Ctrl + Shift + Enter

seems to return a time.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Lee" wrote in message
...
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.
=SUMPRODUCT(--(ISNUMBER(A1:A58/24)),ISNUMBER((A1:A5-8/24))*(SUM(A1:A5)*24<40))
did not work. This formula ignores both word and time entries when I
applied
it.


--
Lee D.

--
Lee D.