counting times

**dodger999** · 08-25-2009, 12:47 PM

Guys just a quick one,
in cell F5 I would like to know how to count the times between 13:00 am and 14:00 so if theres 3 times it will show a 3 i havnt got a clue what formula to use thanks anybody who can help

**DonkeyOte** · 08-25-2009, 12:50 PM

Based purely on your sample file, one method might be:

D5: =SUMPRODUCT(--(HOUR($B$5:$B$11)=10+COLUMNS($D5:D5)))
copied across as required

**dodger999** · 08-25-2009, 01:03 PM

Thanks for the quick reply but being the novice i am im a bit lost even though it does work i dont understand it sorry

**DonkeyOte** · 08-25-2009, 01:38 PM

It counts the number of entries in B where the HOUR of said entry equates to the requisite value be it 11, 12 etc...

The requisite hour (11, 12, 13 etc...) is determined by taking 10 and adding to that the number of columns in the specified range... in the first column that increment will be 1 (one column in range $D5:D5) therefore hour 11, when the formula is copied to E5 the increment will become 2 given there are two columns present in the range $D5:E5 and therefore making hour = 12 ... and so on and so forth.

For more info. on SUMPRODUCT and coercion (--) refer to the link in my signature.

**dodger999** · 08-25-2009, 02:11 PM

Many thanks for the explanation

**dodger999** · 08-25-2009, 02:35 PM

Sorry 1 other problem thats just hit me is i should have put in AM times i:e cells should read 00:01 after midnight so 13:55 should really be 01:55 how do i get around this problem sorry to be a pain