Should I use Sumifs or Sum product.

[jeci80]

Hello All,

I am trying to calculate the total amount of kilos for a specific date with a given time range.
As well as the average time they have been handled with in the same specifications.

attached is a sample sheet of the info I am working with but I cant seem to get the formulas to work.

Can someone please help!!!!!!!

Thank you! Book1.xlsx

[bebo021999]

Some ranges in your SUMIFS are different: change all to row 14 to 41
B2=SUMIFS($G$14:$G$41,$B$14:$B$41,">=08:00:00",$D$14:$D$41,"<=16:30:00",$A$14:$A$41,B1)

[AlKey]

you can also use cell references for the time


=SUMIFS($G$14:$G$41,$B$14:$B$41,">="&$I$4,$D$14:$D$41,"<="&$I$5,$A$14:$A$41,B1)

[Pete_UK]

You need to make sure that the ranges are the same - for example, in B2:

=SUMIFS($G$14:$G$41,$B$14:$B$41,">=08:00:00",$D$14:$D$41,"<=16:30:00",$A$14:$A$41,B1)

(you had $13:..$40 for the first 3 ranges).

Hope this helps.

Pete

[bebo021999]

BTW, if there is "over midnight" counted, date and time range must be combinated by SUMPRODUCT, like this:
=SUMPRODUCT((date_range+time_range>=date1+time1)*(date_range+time_range<=date2+time2)*value_range)

[jeci80]

Ok perfect so that worked great. now another question , if you look at the next range the times go over until the next day. It should not give me any problems with calculating the total amount of kilos but it will with the average times. Im guessing on the average column (E) I need to insert a formula so that it will go over to the next day and still give me the same time kilos were handled..

any ideas on that formula?

Thanks so much for all your help.

[Pete_UK]

Bebo has already anticipated that question - see Post #5.

Pete

[jeci80]

Thank you!!!! I just skipped right over it!

[jeci80]

Now as I am not exactly the pro at this can you show me how it works in the book I attached. Sorry. I still get really confused with the sumproduct formula.

Thanks!!!!!

[bebo021999]

OK.
Try in B2:

Please Login or Register  to view this content.

B3:

Please Login or Register  to view this content.

Adjust time for B4 and B5