+ Reply to Thread
Results 1 to 5 of 5

Count of all cells and Frequency of distinct cells when time crosses midnight

  1. #1
    Registered User
    Join Date
    08-04-2012
    Location
    Rochester, New York
    MS-Off Ver
    Excel 2010
    Posts
    13

    Count of all cells and Frequency of distinct cells when time crosses midnight

    Based on how to find the MAX time value after midnight in a range I attempted to count the distinct number of items in the range. I was unsuccessful. Attached is the example spreadsheet. The issues is with the Route Breakdown by Day tab in columns F and G.

    For column F the simple =COUNTIFS('Table 1'!$A$2:$A$353,A2,'Table 1'!$C$2:$C$353,'Route Breakdown by Day'!B2) formula does not account for items past midnight (as they fall on a different day).

    for column G, its the same issue. {=SUM(IF(FREQUENCY(IF('Table 1'!$F$2:$F$353=$H2,MATCH('Table 1'!$B$2:$B$353,'Table 1'!$B$2:$B$353,0)),ROW('Table 1'!$B$2:$B$353)-ROW('Table 1'!B2)+1),1))}

    How can I get an accurate count of all items (column F) and unique items (column G) when the date/time crosses midnight.

    For the highlighted example on Route Breakdown tab the answer for F3 should be 19(not 10) and G3 should be 7(not 5).

    Thanks for your time in advance. Example w Formulas.xlsx

    p.s. on a side note, when running my VBA script the Frequency formula above takes a huge % of the total macro time. I have a spreadsheet which runs whole VBA script in 52seconds but 36seconds of it is that is the one formula being calculated. Is use of Frequency a huge excel processor suck?

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Count of all cells and Frequency of distinct cells when time crosses midnight

    Try pasting this formula into Table1!F2 and then copy down:
    Please Login or Register  to view this content.
    Then paste this formula Route Breakdown by Day!F3:
    Please Login or Register  to view this content.
    Both the values in Route Breakdown by Day!F3 and Route Breakdown by Day!G3 will then show correct numbers.
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Count of all cells and Frequency of distinct cells when time crosses midnight

    Your file has a Time and Route Start Time but no Stop time. How is one to tell when the Stop Time is?
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    08-04-2012
    Location
    Rochester, New York
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Count of all cells and Frequency of distinct cells when time crosses midnight

    JeteMc, I'll be a monkey's butt... That was a very elegant solution. I almost cant believe it was that easy - now that I see what you've done. As opposed to trying to come up with a more complex solution you found a way to use a simpler one. Brilliant.

    My macro still takes the same length of time due to the Frequency formula but I guess that is a problem for another day. Thanks again.

    Newdoverman, the stop time will vary but will always be before 8am the next day.

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Count of all cells and Frequency of distinct cells when time crosses midnight

    You say it is before 8am but where in your data is that indicated? Without knowing when the stop time is, there is no calculation that will account for times that cross midnight if it is not known when the stop time is.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] SUM FREQUENCY formula to count distinct values w/ OR condition
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2015, 03:55 PM
  2. [SOLVED] Counting values in a time range - when the time range crosses midnight
    By dlocos in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-24-2014, 04:27 AM
  3. Formula for Time Sheet that Crosses Midnight
    By imjuspiayin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2013, 10:24 PM
  4. count time after midnight
    By tcowen in forum Excel General
    Replies: 11
    Last Post: 10-17-2011, 10:18 AM
  5. Count time after midnight
    By mannme in forum Excel General
    Replies: 6
    Last Post: 10-29-2009, 02:25 PM
  6. Lock Cells At Midnight
    By gandolff in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-29-2009, 11:51 PM
  7. Ticks/Crosses in cells?
    By melvin in forum Excel General
    Replies: 2
    Last Post: 01-28-2005, 06:21 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1