# Formula to separate data

Hi there

I have a list of times all in random order for each day of the month when calls were taken. Is there anyway I can write a formula to say: certain amount of calls were taken between 07:00 - 07:59, certain amount of calls were taken between 08:00 - 08:59, so on and so on....?

edit retag close merge delete

Sort by » oldest newest most voted Yes, it's very easy and simple calculation.

=SUMPRODUCT(N(HOUR(A:A)=11))


will counting calls from 11:00:00 to 11:59:59,99

more

Hey sorry I'm not so savvy with these sorts of things. Just to confirm I do =SUMPRODUCT(N(HOUR(A:A)=11)).

What do I put where the 'N' is?

'A:A' Are these my cells?

=11... What is this for?

Sorry for being so ignorant! I'm on a learning curve. Thank you

Yes, most of it you understood quite correctly: A:A is really a column with time, 11 - it is start of calculated interval (11:00 AM), just change it to 8,9,10 etc for each hour in day. About N() - it is Calc's function to convert boolean value (result of compare - TRUE or FALSE) to integer (1 or 0)