# Suming Time brackets

#### Peter1973

##### Well-known Member
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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Lewiy

##### Well-known Member
Something like this?:
Book1
ABCD
118:45512
218:556
319:102
419:303
519:457
620:208
720:409
Sheet1

#### Peter1973

##### Well-known Member
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

##### Well-known Member
Perhaps for the second argument use:
Code:
``--(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.
Code:
``=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

##### MrExcel MVP
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

##### Well-known Member
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

Replies
8
Views
896
Replies
16
Views
715
Replies
1
Views
504
Replies
18
Views
935
Replies
4
Views
717

1,190,786
Messages
5,982,916
Members
439,807
Latest member
WXM86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back