Suming Time brackets

[Peter1973]

Can anyone help me with this ?
In A1:A100 i have various times ie 19:00, 21:00 22:00, 23:15 etc
and in column B1:B100 I have value ie 10.5 etc

I would like a formula to look up a certain time bracket in A1:A100 then in the relvant columns in B1:B100 summ the values.

So for example it would give me total value of all numbers in column B which a time hour of say 19 in column A.

Help appreciated.

 

[Lewiy]

Something like this?:

Book1
	A	B	C	D
1	18:45	5	12
2	18:55	6
3	19:10	2
4	19:30	3
5	19:45	7
6	20:20	8
7	20:40	9
Sheet1

 

[Peter1973]

Thanks just one question what do I do when the hour is between 23:00 and 00:00 as it sends back zero value.

 

[Lewiy]

Perhaps for the second argument use:

--(A1:A7<=TIME(23,59,59)),

i.e. use a "less than or equal to" operator. If you wanted to go between say 23:30 and 00:30, you can just keep adding seperate arguments into the formula in the same way, i.e.

=SUMPRODUCT(--(argument1),--(argument2),--(argument3),--(argument4),sumrange)

The reason it returns a zero value is because whn Excel looks at time, it only considers a single 24hr period, so you can't possibly have a time that is greater then 23 and less than zero.

Hope that helps!

 

[HalfAce]

Hello Peter1973,
Another idea that does not include 'hard coding' the hour of interest in your formula:
Enter this formula somewhere outside of the rows containing your times & values.
=SUBTOTAL(9,B1:B100)

With that in place you can highlight the range of times in column A and:
1) From the menu - Data > Filter > AutoFilter
2) (After opening the AutoFilter dropdown) choose Custom > Begins With
3) In the field to the right of 'Begins With', simply enter the hour of interest (ie, 19, 23, 0) etc.

Your subtotal formula should return the sum of the filtered values in column B.

Hope it helps.

 

[Peter1973]

Thanks thats done the trick just a quick one im using hte formula
=SUMPRODUCT(--(HOUR(Sheet1!$B$3:$B$61)=23))

But cant seem to figure out how to use this to sum all times with Hour 00:

any ideas