Calculate based on date and time range

Hi,

I have a file that is already totaling needed values based on an entered date range.
Now I would like to see if a way exist to narrow it by time of day also.
One problem is the time is recorded in two columns with the AM/PM in the second column.

new.jpg

Looking at the attached file can the formula in the green box be adjusted to accept a time range being added in the red boxes?
It would still need to work even if the time range is left blank.

I'd love to know how to chart this in 8 hour increments but that is another day.

Thanks for any suggestions.

Hi,

Just extend the SUMIFS to include column D & the J3:K3 cells. i.e.

Formula:

Please Login or Register  to view this content.

=sumproduct(($c$5:$c$357+$d$5:$d$357+($d$5:$d$357<=0.5)*($e$5:$e$357="pm")*0.5>=$h3+if(or($j3=0,$k3=0),0,$j3))*($c$5:$c$357+$d$5:$d$357+($d$5:$d$357<=0.5)*($e$5:$e$357="pm")*0.5<=$i3+if(or($j3=0,$k3=0),1,$k3))*($b$5:$b$357))

Please Login or Register  to view this content.

Hi,

Personally I'd add a helper column and join all three columns C:E
i.e. in F5 copied down

Formula:

Please Login or Register  to view this content.

Then your SUMIFS is simplified to

Formula:

Please Login or Register  to view this content.

Hi,

Yes indeed. A common solution is to hold the master formula above the data somewhere and then either copy it and paste it to the new rows, then copy the formula you've just put in the new rows and paste back as values.

Personally I just create a macro to perform the task above if the file is getting large solely because of the formulae.

However I'd be interested to hear how big the file is and how many rows the formula has been copied to. Are you sure it hasn't accidentally been copied to all 1 million + rows?

The hope is to not have to come back and paste in formulas as the data grows.

Right now only one data source has been added. Once all is working as desired 7 more sources will be referenced so all can be consolidated.

File size is just over 10 megs. And sheepishly admitting that the formula has been pasted to countless cells - the entire column.
So, can you point to a posting that simply shows how to use a macro as mentioned? One that I suppose would look at a cell, determine that data is present, then add a formula to a cell in designated column on same row as new data that is recognized.