Prev: Inserting a blank row between rows of data
Next: how do I sumif on a font color in excel 2007?
From: NEHicks on 26 Jan 2010 21:09
Col A
09:15
12:39
10:15
09:25
11:36

I need to count the number of times there is an entry in column A that is
between 08:00-08:59, 09:00-09:59, 10:00-10:59, etc.

So the sum would be 08:00-08:59 0
09:00-09:59 2

etc.
From: Teethless mama on 26 Jan 2010 21:26


"NEHicks" wrote:

> Col A
> 09:15
> 12:39
> 10:15
> 09:25
> 11:36
>
> I need to count the number of times there is an entry in column A that is
> between 08:00-08:59, 09:00-09:59, 10:00-10:59, etc.
>
> So the sum would be 08:00-08:59 0
> 09:00-09:59 2
>
> etc.
From: Teethless mama on 26 Jan 2010 21:27
=SUMPRODUCT(--(HOUR(A1:A5)=9))


"NEHicks" wrote:

> Col A
> 09:15
> 12:39
> 10:15
> 09:25
> 11:36
>
> I need to count the number of times there is an entry in column A that is
> between 08:00-08:59, 09:00-09:59, 10:00-10:59, etc.
>
> So the sum would be 08:00-08:59 0
> 09:00-09:59 2
>
> etc.
From: Chelsea on 26 Jan 2010 23:05
Hi,Nehicks,

try

=SUMPRODUCT((($A$1:$A$5)>=TIME(8+ROW()-1,0,0))*(($A$1:$A$5)<TIME(8+ROW(),0,0))*1)

p.s. make sure the data format is time

HTH

Chelsea

"NEHicks" <NEHicks(a)discussions.microsoft.com> wrote in message
news:4D39A3A7-8072-47BA-AC9B-DF74A828162B(a)microsoft.com...
> Col A
> 09:15
> 12:39
> 10:15
> 09:25
> 11:36
>
> I need to count the number of times there is an entry in column A that is
> between 08:00-08:59, 09:00-09:59, 10:00-10:59, etc.
>
> So the sum would be 08:00-08:59 0
> 09:00-09:59 2
>
> etc.

 | 
Pages: 1
Prev: Inserting a blank row between rows of data
Next: how do I sumif on a font color in excel 2007?