×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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.

RE: VBA Excel...Sum cells in groups of Time!!!

An SQL Statement in MS Access would look like this
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!!!

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,"<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!!!

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close