Adding up in time intervals

[woollyg]

Hi there,

I'm trying to add bits of data up in the table below so I can see the total at different 15 minute intervals and then plot it onto a graph, I've tried using sum and sumifs but the formula doesn't seem to work! Any help would be very much appreciated.

Start Time	End Time	Number
10:00	10:05	56
07:00	12:00	12
10:00	10:15	24
07:00	09:00	36
10:00	12:00	16

<tbody>
</tbody>

I would then like to have the time in a column such as 07:00, 07:15, 07:30.... and then in the adjacent column add up the numbers if it is within the range of time intervals.

Thanks for any help!

 

[lrobbo314]

I have your original table in A1:C6. Then, beginning in E2 and copied down, I have all of the 15 minute intervals from midnight - 11:45pm. Then, the formula in F2 is below, then copied down.

=SUMPRODUCT((E2>=$A$2:$A$6)*(E2<=$B$2:$B$6)*($C$2:$C$6))

 

[woollyg]

That's great thanks very much, please may you explain to me what the sumproduct does to the array of data because i have never used it before!

 

[lrobbo314]

(E2>=A2:A6) and (E2<=A2:A6) are looking at your times and seeing if E2 is between those times. Those two things are multiplied against eachother to get an array of ones and zeroes. The ones are true and the zeroes are false. Then it multiplies that against C2:C6. Anything that had a 1 will now have a value of the corresponding value from C2:C6. Then it adds all of those values together and returns the sum. That's where the name SumProduct comes from. It is giving you the sum of the product of the arguments that you enter into the formula.