INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

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.

Jobs

How Can I do this in Excel 2016?

How Can I do this in Excel 2016?

(OP)
Hello All,
I have a specific task to perform in Excel 2016. I have a list of mechanic names who have worked certain number of hours. Example,

Name Hours
Mark 9
Mike 8
Smith 9
Mike 10
Mark 3
Gary 5
Mike 2
Gary 7
and the list goes on to 2000 lines


I want to add all the hours per mechanic name. So, it only gives me one mechanic name with the sum of all the hours infront of it and no name should repeat.
For example, here is how I want the above to be:

Mark 12 (sum of hours)
Mike 21 (sum of hours)
etc

Is there any easy way to do this? please let me know in steps. Thanks

RE: How Can I do this in Excel 2016?

If you don't have two mechanics with the same name, you can do a sort on the name column... and then sum all the values for that name.

Dik

RE: How Can I do this in Excel 2016?

3
You select the entire table and Insert Table, followed by Insert Pivot Table

Select both Name and Hours fields, and the pivot table will give you a summary of hours by name as well as a grand total

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

RE: How Can I do this in Excel 2016?

IRS: Thanks... didn't know that... much easier...

Dik

RE: How Can I do this in Excel 2016?

It seems to be much easier to create them now, as well. I hadn't actually used them in a while, because they seemed messy to set up, but the setup seems much more straightforward now.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

RE: How Can I do this in Excel 2016?

You can also use SUMIF, see screenshot below. I have set the data range as a Table, but you don't have to.

Enter the formula as shown for the first name, with the first and last ranges absolute and the name range relative, and copy it down

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: How Can I do this in Excel 2016?

I do this task like IDS all the time
but, especially if someone else is doing data entry for me, add a validation to the cell with names in case of a misspelling, or, less obvious, adding mistakenly a blank space after a name. for example mike(space) (5 letters) will not be added to something looking for mike (4 letters)
likely there is a more elegant way, but this works for me

RE: How Can I do this in Excel 2016?

Great information!!! Thanks to all who replied.

RE: How Can I do this in Excel 2016?

It appears that Doug has made the table a Structured Table (ST). I, too, strongly commend the use of ST to you.

Using the default ST Table Name, Table1 and an alternate aggregation function SUMPRODUCT()...

C13: =SUMPRODUCT((Table1[Name]=B13)*(Table1[Hours]))


Using either ST notations or Named Ranges makes the formula much more understandable and maintainable.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How Can I do this in Excel 2016?

can you use the trim function to remove leading and trailing blank spaces?

Dik

RE: How Can I do this in Excel 2016?

What spaces?

If you're referring to parsing the values from this page into columns in Excel, use

Data > Text to columns...DELIMITED on SPACE.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How Can I do this in Excel 2016?

by spaces, i simply am meaning data entry error...
as example, the formulae below are the same as IDS above, but answers differ because data entered differently
eg.... "Mike" (4 letters) vs "Mike " (with space at end)

edit.... see my own data entry error.... i meant "Mark" vs "Mark "
haha



RE: How Can I do this in Excel 2016?

Leading or trailing spaces in text fields are anomalies that ought to be caught and corrected way before you get to applying formulas or doing any sort of analysis.

Using Trim() is a crutch to compensate for bad data and, yes, it can and should be used if the data is so corrupted.

But I'd clean up my data before doing any such analysis.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How Can I do this in Excel 2016?

Those are things that you can clean up, after the fact. But, you are having trouble with that' your data entry should be protected from that, such as when someone enters "Mirk." Was that supposed to be Mark or Mike or a new employee named, "Mirk?" There are at least 3 other possible errors that you cannot use the table, structured or pivot, to correct, nor should you:
> wrong name
> wrong hours
> both

Valid data needs to be vetted at the point of entry, not days or weeks later, by someone who might not even know who entered the data in the first place.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

RE: How Can I do this in Excel 2016?

...and I just noticed this...

Quote (triangled)

edit.... see my own data entry error

You can minimize such self-inflicted wounds, by leveraging Excel features like Data > Remove Duplicates. I copied the Name list, pasted it below the table (really NOT the best place to put ANY data) and then applied the Remove Duplicates feature, which minimized such data-entry errors.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How Can I do this in Excel 2016?

in my case, i often have others enter such data for me.
i use data > data tools > data validation > settings > list
which verifies accurate data input at time of input

RE: How Can I do this in Excel 2016?

...but not this time.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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!


Resources


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