COUNTIF with Time and Name Criteria

**jt_tj_ca** · 02-02-2010, 11:10 AM

Hi,

In column G I have a range of times in the format 12:04:44 AM. Beside it I have a series of names EG:

12:05:54 AM Boss
12:09:10 AM Boss
12:04:32 AM Jet
12:08:27 AM Jet
12:03:41 AM Liam
12:09:10 AM Boss
12:03:41 AM Liam
12:04:45 AM Liam
12:05:45 AM Liam
12:05:14 AM Liam

I'm using the COUNTIF formula to tell me how many entries in G are equal to or greater than 12:04:44 AM for Liam.

I've tried both COUNTIF and SUMPRODUCT. Any ideas?

Thanks

J.

**martindwilson** · 02-02-2010, 11:16 AM

=SUMPRODUCT(--(A1:A10>=TIMEVALUE("12:04:44 am")),--(B1:B10="liam"))
or just put time in a cell say E1 and liam in a cell say e2

=SUMPRODUCT(--(A1:A10>=E1),--(B1:B10=E2))

**jt_tj_ca** · 02-02-2010, 11:34 AM

Hi,

The 2nd formula worked for the most part, however a few values end up being missed.

EG:

12:03:30 AM Boss
12:03:50 AM Boss
12:05:09 AM Boss
12:04:29 AM Boss
12:04:25 AM Boss
12:05:48 AM Boss
12:05:37 AM Boss
12:03:09 AM Boss
12:05:33 AM Boss
12:05:37 AM Boss
12:06:03 AM Boss
12:04:02 AM Boss
12:07:17 AM Boss
12:05:54 AM Boss
12:09:10 AM Boss

Using the =SUMPRODUCT(--(A1:A10>=E1),--(B1:B10=E2)) example, I received returns. However, instead of receiving 9, it only returned 8 as having met the criteria.

Thoughts?

Thanks,

J.