countif between a date/time range

hi,

how can i use countif or sumproduct or sumif ..etc to calculate how many occurrence in a specify date and time .

Pls see example.

Are you looking to return the results as per your sample - ie a 1 in the appropriate column on each row ? Or are you looking to do a cumulative count in one cell for each time frame ?

Given your bands aren't even (03:00 -> 15:00, 15:01 -> 03:00, 03:01 -> 15:00 etc...) a common formula is obviously little convoluted so keeping things simple(ish):

If we assume

Please Login or Register  to view this content.

then

Please Login or Register  to view this content.

then

Please Login or Register  to view this content.

First of course you must ensure the values in B are date time values... when I opened your file they were numbers stored as text rather than being true numbers, to convert run Text to Columns on Column B (click Finish).

It's not really any easier - you still need to coerce values in B to be numeric and specify start / end times for each column to ascertain band
(and I would not to say the prior approach is overly complex)

OK that's good but I don't understand the need for the formulae in row 3 onwards.

Change the COUNTIFs to SUMIFs, eg:

D4: =SUMIF($B:$B,">="&D$2,$C:$C)-SUMIF($B:$B,">="&E$2,$C:$C)