Saturday, November 11, 2017

yogi_Count Number Of Form Submisions By Hour For Each Date

Google Spreadsheet   Post  #2278

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-11-2017
question by: Selfless Crustacean
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!topic/docs/zbRdM5VVfZM;context-place=forum/docs
How do I count the number of values between two times?

https://docs.google.com/spreadsheets/d/1xwhvKUuS74JzMwzw6PfAhT6Cn_vmeM7IAxvjxmoq7m8/edit#gid=0 

Please make a copy. So far, this is a manual process. I've inputted some numbers manually at the moment.

I have a sheet that is usually importrange Sheet4. Sheet4 is a Form response that is dated and time stamped after every submission.

I need to count the number of contacts that come in between two times. (For example 0:00:00 and 1:00:00) on sheet Calls by the hour.

Thinking it would be easier, I've put 1 after each submission so I could do a sumif formula between to times but can't get it to work.

So far I have =(sumifs(Sheet4!B1:B,Sheet4!A1:A,">="&TIME(15,0,0),Sheet4!A1:A,"<="&TIME(16,0,0)) :Finding the number of contacts between the 15:00:00 and 16:00:00. It looks like I have an issue referencing time on another sheet, alongside another problem of how to get this formula working for all days? /arrayformula.