Iain,

How do you determine whether they are blank? I ask this as I wonder if they
are really empty blank, or a space blank (IYSWIM).

I had this data

28/12/2004 01:59:59
28/12/2004 05:00:00
30/12/2004 01:59:59
28/12/2004

and with
28/12/2004 in both B42 and C42, the < 2 hour test gave me 2 (as
expected).

When I changed the test to 4 hours, I got 1, again as I should? If I
change that blank to a single space, I get 2.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Iain Halder" wrote in message
...
Hi,

Thank you for your reply!

I was thinking the error lay in the area of testing the times but even
applying your remedy makes only a partial correction.

If I say ..... <(--"02:00:00")))

I get the correct answer.

However when I try to apply instead I get a nonsense.

I printed out the DATA I was working with and discovered that when I
calculate for a figure 04:00:00 I get that figure PLUS the figure for
blanks!!! The two separate figures being added together.

I find the whole time calculation becomes correct when I subtract the
total for blanks from the figure for 4 hours.

This cannot be right.

Is there something I am missing here? How can Excel be adding blank
fields (which are surely 0) to fields for 4 hrs?

Iain.H

On Thu, 13 Jan 2005 13:05:02 -0000, "Bob Phillips"
wrote:

Iain,

Good detail, you are hearing us :-).

Although you don't state where the problem lays, dates or times, I think
the
way that you have tested times is incorrect. Instead of {"02:00:00"},
try --"02:00:00", e.g.

b10:

=SUMPRODUCT((DATA!A2:A2000=B42)*(DATA!A2:A2000<= C42)*(DATA!F2:F2000<(--"02
:
00:00")))

Rescued Cats & Kittens Needing Homes
'o'< www.celiahammond.org 'o'<
'o'< www.cat77.org.uk 'o'<