SUMIFS equal or less/more than Time problem

[shenftw]

Hi I've spent the last two hours trying to figure this out. I'm trying to calculate the savings achieved based on amount of Time spent. There are two types of time, Normal and OverTime. I can't figure out why it works for IF but not for SUMIFS. Because it is a range, and has multiple entries, the IF function is insufficient. I tried using the 'Date' method (i.e., ">='&A1) but it doesn't seem to work for some reason and the value always computes to 0. These are examples of the functions.

=SUMIFS(Input!$M$5:$M$5489,Input!$G$5:$G$5489,"Optimized",Input!$H$5:$H$5489,"Crane",Input!$B$5:$B$5489,">="&$C$5,Input!$B$5:$B$5489,"<="&$D$5,Input!$K$5:$K$5489,"<=08:00",Input!$L$5:$L$5489,">=16:00")

Input!$K$5:$K$5489,"<=08:00",Input!$L$5:$L$5489,">=16:00" doesn't seem to work, does anyone know why? And perhaps have a solution?

On the other hand: =IF(AND(Input!$K$5:$K$5489>=I21,Input!$L$5:$L$5489<=I22),SUMIFS(Input!$M$5:$M$5489,Input!$G$5:$G$5489,"Optimized",Input!$H$5:$H$5489,"Crane",Input!$B$5:$B$5489,">="&$C$5,Input!$B$5:$B$5489,"<="&$D$5)*Menu!$AL$24)

This computes the right number, but I need more conditions to be met...

Please and Thank you.

[David A Coop]

Welcome to the forum.

It is always advisable to attach a sample workbook. It makes it easier for forum members to understand your issues.

That said, I think your problem comes from using time in the SUMIFS() formulas. Try using .33333333 (8/24) and .66666666 (16/24) instead.

Please Login or Register  to view this content.

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.

If this has been of assistance, please advise.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[shenftw]

Thanks David, that pretty much worked. I do have a 24hr problem now though. If I want to get a range between say 1600 and 0100 hours, it won't compute. This is what I've got so far:

=SUMIFS(Input!$M$5:$M$5489,Input!$G$5:$G$5489,"Optimized",Input!$H$5:$H$5489,"Crane",Input!$B$5:$B$5489,">="&$C$5,Input!$B$5:$B$5489,"<="&$D$5,Input!$K$5:$K$5489,">=16:00",Input!$L$5:$L$5489,IF(Input!$L$5:$L$5489<TIME(8,0,0),"<8:00",IF(Input!$L$5:$L$5489>TIME(16,0,0),">16:00")))

Any ideas?

[shenftw]

I also tried this, which only works half the time. and is driving me crazy.

=SUMIFS(Input!$M$5:$M$5489,Input!$G$5:$G$5489,"Optimized",Input!$H$5:$H$5489,"Crane",Input!$B$5:$B$5489,">="&$C$5,Input!$B$5:$B$5489,"<="&$D$5,Input!$K$5:$K$5489,">=16:00",Input!$L$5:$L$5489,IF(Input!$L$5:$L$5489<TIME(8,0,0),"<8:00",">16:00"))

[David A Coop]

Please post some sample data - I don't need the whole 5,000 rows - just enough to cover both scenarios.

David

[shenftw]

I think it's loaded. Is it sufficient?

[shenftw]

uhh... bump? does anyone know how to solve this?

[quekbc]

So, assuming all else being equal (i.e. Crane, Optimised, and falling between the dates), if it's not Normal savings, it must be OT savings right?

If so, you can just do a SUMIFS of the first 3 conditions (Crane, Optimised, start/end dates), and minus off whatever that falls within Normal savings.

I.e. formula in G4 is

Please Login or Register  to view this content.

[shenftw]

Thank you. Mind is blown. So easy I want to cry. Thanks again!

[quekbc]

You're welcome. It happens to the best of us.

If this resolves your initial query, can you kindly mark this thread as solved by clicking on Thread Tools above your initial post and clicking on Mark This Thread as Solved.