VBA Excel...Sum cells in groups of Time!!!
VBA Excel...Sum cells in groups of Time!!!
(OP)
I need help.
I have a row of information including "Time" and "Sites". I want to add up the sites by hour. For example I want to add up the sites for all the 7 O'Clocks, add up the sites for all the 8 O'Clocks, and so on...
The table of information look like this...
TIME SITES
7:00 150
7:00 300
7:15 400
7:30 50
8:00 100
8:00 115
9:00 35
9:30 65
How would I sum up the sites under each time, but keep anything that begin with 7, such as 7:00, 7:15 and 7:30 within the same group???
Another thing I would like to do is everytime there is a change from a 7 O'Clock to an 8 O'clock, to insert a row in between them. Is that possible. I would appreciate any help.
Thanks for your time.
I have a row of information including "Time" and "Sites". I want to add up the sites by hour. For example I want to add up the sites for all the 7 O'Clocks, add up the sites for all the 8 O'Clocks, and so on...
The table of information look like this...
TIME SITES
7:00 150
7:00 300
7:15 400
7:30 50
8:00 100
8:00 115
9:00 35
9:30 65
How would I sum up the sites under each time, but keep anything that begin with 7, such as 7:00, 7:15 and 7:30 within the same group???
Another thing I would like to do is everytime there is a change from a 7 O'Clock to an 8 O'clock, to insert a row in between them. Is that possible. I would appreciate any help.
Thanks for your time.





RE: VBA Excel...Sum cells in groups of Time!!!
SELECT Format([time],"hh") AS Expr1, Sum(Table1.sites) AS SumOfsites
FROM Table1
GROUP BY Format([time],"hh");
The results would look like
Expr1 SumOfsites
07 900
08 115
09 100
The format function converts the times to the first two values in the time. I'm not familar with the grouping operations in excel but I think Microsoft Query would allow you to do this.
Sorry I couldn't help you more.
Gerald Austin
Iuka, Mississippi
http://www.weldinginspectionsvcs.com
RE: VBA Excel...Sum cells in groups of Time!!!
=SUMIF(A1:A20,"=07:00",B1:B20) for example.
I cannot find away of having more than two conditions in a single SUMIF funtion, so summing for the rnage 07:00 to 08:00 is a bit trickier. However, it can be done using the difference of two SUMIF formulae.
=SUMIF(A1:A20,">=07:00",B1:B20) in one cell
=SUMIF(A1:A20,"<08:00",B1:B20) in another
The sum of all sites between 07:00 and 08:00 is the difference of the two.
Tom
RE: VBA Excel...Sum cells in groups of Time!!!
Thread770-41751
Thread770-42106
that are wanting to do something like this - count and sum based on multiple criteria.
Look at these and consider if Array Formulas will do the trick.
These are pretty much like the SUMIF function tomatge mentions, but they will allow you to work with multiple criteria.
You are working with time. so you may also have to do some data massaging... maybe. I'm not very exepienced with handling time formats.
This is not really a visual basic solution, but maybe it will get you started.
good luck,
ProjEngKLS