+ Reply to Thread
Results 1 to 4 of 4

Sumifs between times

  1. #1
    Registered User
    Join Date
    03-03-2024
    Location
    NZ
    MS-Off Ver
    365
    Posts
    2

    Question Sumifs between times

    Hi there, i have a table of data that has times from 00:00:00 to 23:59:00 with volumes of power usage for 5 minute intervals. I am trying to use a sumif function to sum peak and offpeak amounts using time:

    Peak = SUMIFS(Mvmt!F3:F8353,Mvmt!$B$3:$B$8353,">=07:00:00",Mvmt!$B$3:$B$8353,"<=20:59:00")
    Where column F= is my usage to sum and column B = the 5 minute time interval.

    Offpeak = SUMIFS(Mvmt!F3:F8353,Mvmt!$B$3:$B$8353,">=21:00:00",Mvmt!$B$3:$B$8353,"<=06:59:00")

    I suspect it has something to do with a 24 hour clock and it not knowing that 00:00 is after 23:00 but cant work a better formula.

    Any ideas?

    Ta

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: Sumifs between times

    your first one works for me...= SUMIFS(Mvmt!F3:F8353,Mvmt!$B$3:$B$8353,">=07:00:00",Mvmt!$B$3:$B$8353,"<=20:59:00")

    and this works as a change for your off-peak... = SUMIF(Mvmt!$B$3:$B$8353,">=21:00:00",Mvmt!$F$3:$F$8353)+SUMIF(Mvmt!$B$3:$B$8353,"<=06:00:00",Mvmt!$F$3:$F$8353)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    03-03-2024
    Location
    NZ
    MS-Off Ver
    365
    Posts
    2

    Re: Sumifs between times

    Thanks - that seems to work Well done

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: Sumifs between times

    you're welcome, glad I could help! AND thank you for the rep!

+ 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. formula to recalculate lap times when actual times vary from goal times
    By dan76n in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-24-2022, 05:52 AM
  2. [SOLVED] SUMIFS - Between Dates & Times
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-15-2020, 01:17 PM
  3. Replies: 0
    Last Post: 08-22-2017, 03:49 PM
  4. [SOLVED] Averaging the earliest start times and latest end times for multiple days
    By kbiro in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-26-2016, 02:15 PM
  5. If a search term can be found 1 time, 2 times 3 times 4 times 5 times
    By excelcandy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2013, 09:57 PM
  6. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  7. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 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