+ Reply to Thread
Results 1 to 3 of 3

SUMIFS with range of time and creteria

  1. #1
    Registered User
    Join Date
    09-20-2018
    Location
    Araraquara, Brazil
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    2

    Exclamation SUMIFS with range of time and creteria

    Hello Guys, I’m appealing to the experts on this board for assistance in a problem that I have been unable to find a solution to (my appologies if this has already been previously posted -- I couldn't find it).
    My situation is:
    I need to create an overview per Product and Range of time:
    My criteria are: The formula should Find the type of Product and SUM If it’s upper than Start hour (COL I) and lower than End Hour (COL J), and also make the sum in the night shift (i.e. from 22:00 PM to 0:30 AM or 1:00 AM to 6:00 AM). So that's why I choose the formula SUMIFS

    Table A - I input de information manually on the following colluns:
    COL C – Type of Product
    COL D – Start Pause
    COL E – End Pause
    COL F – Variation between COL C and COL B

    Table B - Overview by Type of Product and per range of time.
    COL H – Type of Machine
    COL I AND J – Start and End Period
    COL K – FOMULA


    =SUMIFS($F:$F,$C:$C,$H$4,$D:$D,">="&I4,E:E,"<="&J4)

    I trully hope that I'm clear as possible in my explanation. I appreciate any feedback.

    Thanks in advance,
    Eduardo Rocha
    Attached Files Attached Files
    Last edited by durochaa; 09-23-2018 at 04:19 PM.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: SUMIFS with range of time and creteria

    Try this one in K5 and fill down

    =SUMPRODUCT((Tabela5[Product]=H5)*(Tabela5[Start] >=I5)*((Tabela5[End]+(Tabela5[End]< Tabela5[Start])) <=(J5+(J5<I5))),Tabela5[Variation])

    Does that give you the results you expect?

    I'm assuming that I14 should be 3:00 A.M. not 6:00

  3. #3
    Registered User
    Join Date
    09-20-2018
    Location
    Araraquara, Brazil
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    2

    Re: SUMIFS with range of time and creteria

    Yep, It's exactly what I was in need
    Thank you so much man.

+ 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] Sumifs with date criteria, but the range includes time with the date
    By Alphabex in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-08-2017, 12:08 AM
  2. SUMIFS - Across Date Range, Time Range, and by City
    By awilliams8976 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-09-2016, 06:51 PM
  3. Copy data from multiple workbooks to one single workbook with some range creteria
    By naresh73 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-27-2015, 01:34 PM
  4. sumifs for time range
    By PLD60 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-06-2014, 04:33 AM
  5. sumifs on a pivot table - compare range with range
    By pavlos in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2013, 02:26 AM
  6. [SOLVED] Getting data from a range matching certain creteria
    By cndesu in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-29-2012, 10:00 AM
  7. Formula help with SUMIFs and time range
    By Maricha in forum Excel General
    Replies: 9
    Last Post: 08-08-2011, 02:52 PM

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