Sumifs with multiple interval time range (30min intervals - 24 Hours)

[rajreddy]

Hi,

I need help with the Sumifs multiple interval time range. It is giving the wrong output with the formulae I used.
Please help. Attached is the sample file.

Regards
Raj

 

[rahulshewale1]

hi @rajreddy ,

Please provide Desire result ( output ) manually .

What if 7:00-16:00 then ?

 

[rajreddy]

hi @rajreddy ,

Please provide Desire result ( output ) manually .

What if 7:00-16:00 then ?

Coloumn O
Cell O13 to O36 is manually updated - Highlighted in Red
Please help


Regards
raj

 

[vletm]

rajreddy
Press [ Refresh ] to refresh results after changes...
... comments shows, which values has counted
> File updated <

 

[rajreddy]

Hi
Thank you. Is there an excel function which helps in getting the output so that it can be easily done. When I click on refresh it is just loading and never ending. Macro seems to be time consuming. Can you please help with the excel function to get the output.

Regards
Swaraj

 

[rajreddy]

Macro is awesome with the output. Thank you for the help .
Just checking if there is function that helps to get the output so that I can integrate that into my template. Because macro is taking longer time.

Regards
Raj

 

[vletm]

rajreddy
I rechecked that file - here it works.
I added note then ready as well as how long time it has taken.
For me, it takes about one second, normally less.
How quick should You get Your output?

 

[rajreddy]

It working fast if no other files are open. I am good with the macro.
I have one more request in the file, can we move Shifts which are in two days (example 17:00-02:00, 21:00-06:00, post 12:00 AM those numbers should be added to next day, sunday numbers should be moved to Monday, Monday to Tuesday so on and so forth.

Please help.

 

[vletm]

rajreddy
You missed to answer: How quick should You get Your output?
... and Sat to ...next Sun

 

[rajreddy]

How quick should You get Your output? I am good with the speed now.
Template is awesome. Thank you so much for the great help.

Can you guide me on how to learn macros step by step, if there is any step by step method to learn and improve.

Thank you

 

[vletm]

How quick should You get Your output?
I would get answer like:
less than 12 hrs - less than six hours - less than one hour - less than 30 minutes - less than one minute - less than one second.

Step-by-step:
Have a challenge
1> try to figure how to do it manually
2> write eg Sub rajreddy() and press <Enter>
3> write one code line based You manually solved idea
-- later - after-hours-and-hours-coding = years - this part could be smoother
4> test how do it work
5> if not, do modifications > step 4)
6> repeat steps 3 to 6 as many times as needed

 

[John Jairo V]

Hi, to all!

This is an option with helper columns. It can be done without them, but I leave it for learning purpose. Blessings!

 

[rajreddy]

Thank you John Jairo Nice logic .

 

[Peter Bartholomew]

I would like to have achieved the entire result with a single dynamic array formula but that proved impractical because SUMIFS requires all the criteria ranges to be references to correctly shaped ranges. As it is, each column is given as a single array, the formula being

= SUMIFS( priorData, end,">"&interval, overnight?,1)
+ SUMIFS( currentData, start, "<="&interval, end,">"&interval, overnight?,0)
+ SUMIFS( currentData, start, "<="&interval, overnight?,1)

where the helper range 'overnight?' is given by

= SIGN(end<start)

and currentData and priorData are relative references constructed using INDEX

= INDEX(data,,1+MOD(dayIdx,7))
= INDEX(data,,1+MOD(dayIdx-1,7))