VBA Excel...Sum cells in groups of Time!!! 1

[aordoqui]

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.

 

[pipewelder1999]

An SQL Statement in MS Access would look like this
SELECT Format([time],"hh&quot 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

 

[tomatge]

Assuming you're using Excel, I think the answer is to use the SUMIF() funtion. If the times are in A1:A20 and the sites are in B1:B20 then you can sum all the values for a particular time using :

=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,&quot;<08:00&quot;,B1:B20) in another

The sum of all sites between 07:00 and 08:00 is the difference of the two.

Tom

 

[ProjEngKLS]

There are couple of threads:

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