Count occurrences and sum values with times spanning over midnight.

**SorZer** · 12-11-2012, 05:42 AM

I am trying to count Count occurrences between two times spanning over midnight. It all works as long as the time not goes over 00:00. This is what I have

A=date and values in 2012-01-02 00:30:00 format
B=Value in 10/20/30 This is some thing I will deal with after i have figured out my occurrences problem

This is what I have come up with =PRODUKTSUMMA(--(TIMME(A2:A50)>=7);(--(TIMME(A2:A50)<=23)))
But this only works when not the time is between say start time:23:23 too 05:45 and it can not specify down two minutes criteria. Any help would be greatly appreciated

**Kevin UK** · 12-11-2012, 07:47 AM

Hi SorZer

Try in:
H3: =SUMPRODUCT(--(A2:A50-INT(A2:A50)>F3),--(A2:A50-INT(A2:A50)<G3))
H4: =SUMPRODUCT(--((A2:A50-INT(A2:A50)>=F4)+(A2:A50-INT(A2:A50)<=G4)))

See your file attached.

Kevin

**SorZer** · 12-11-2012, 07:02 PM

It almost works

.. :-) it keeps on counting blanks as occurrences. And how can i get it to count values at each occurrence.
=PRODUKTSUMMA(--((A2:A1000-HELTAL(A2:A1000)>=F4)+(A2:A1000-HELTAL(A2:A1000)<=G4)))

Becomes 968 occurrences?!?!?

Se new example

**Kevin UK** · 12-12-2012, 04:22 AM

Hi

Well in your original post, the range you were looking in was A3:A50, so that was the same range I used. You changed the range to A2:A1000, where there were 950 blanks, "0". In other words, they were all less then 07:00!

Now in Cell H4 replace the formula with:

Formula:

Please Login or Register  to view this content.

See the attached.

**SorZer** · 12-14-2012, 04:12 AM

Thank you so much!!!! it all works well.

Count occurrences and sum values with times spanning over midnight.

LinkBack

Thread Tools

Rate This Thread

Display

Count occurrences and sum values with times spanning over midnight.

Re: Count occurrences and sum values with times spanning over midnight.

Re: Count occurrences and sum values with times spanning over midnight.

Re: Count occurrences and sum values with times spanning over midnight.

Re: Count occurrences and sum values with times spanning over midnight.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions