How Can I do this in Excel 2016? 4

[masieh2004]

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

 

[dik]

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

 

[IRstuff]

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 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[dik]

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

Dik

 

[IRstuff]

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 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[IDS]

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/

 

[Triangled]

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

 

[James2703]

Great information!!! Thanks to all who replied.

 

[SkipVought]

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()...
[tt]
C13: =SUMPRODUCT((Table1[Name]=B13)*(Table1[Hours]))
[/tt]

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[dik]

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

Dik

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Triangled]

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

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IRstuff]

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 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[SkipVought]

...and I just noticed this...

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Triangled]

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

 

[SkipVought]

...but not this time.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}