1

I am trying to count objects in a list (the list is in a different tab) based on different criteria. As it can be seen in the image uploaded, the criteria are:

  1. Object name
  2. Date
  3. Time enter image description here I have a problem with the third one. How can I count the objects that occur between 13:30 and 14:00. I want to count the occurrences just by referencing to the time in the cells C5, D5, E5 etc. How can I do it?
Filip Toma Isai
  • 21
  • 6
  • 1
    If the times in row 5 are true times then you can use the functions `SumIfs()` with multiple if-criteria and `Hour()` to extract the hour of a time / date from a cell. Then you can sum / count all occurrences where the hour is between 13 and 14. Alternatively, you can also calculate the time fraction for 13:30 which is `0.56` and 14:00 which is `0.58` and sum / count all of those where the fraction is between 0.56 and 0.58. For more information about time / dates you might be interested to read this: http://stackoverflow.com/a/38001028/1153513 OR http://stackoverflow.com/a/37101358/1153513 – Ralph Apr 20 '17 at 13:06
  • I see a clear answer here.. really looking forward to see how the 'solved' "count objects in a list" formula from OP looks like.. ( : – p._phidot_ Aug 08 '18 at 10:14

1 Answers1

0

Just use :

=countifs([urRange1],[urCriteria],[urTime],">=XXX",[urTime],"<YYYY")

with XXX is the value you get from =value(C5) and YYY is the value you get from =value(D5)

Looking forward for OP verification.. ( :

p._phidot_
  • 1,913
  • 1
  • 9
  • 17