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
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?
Dik
RE: How Can I do this in Excel 2016?
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?
Dik
RE: How Can I do this in Excel 2016?
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?
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?
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?
RE: How Can I do this in Excel 2016?
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,
Just traded in my OLD subtlety...
for a NUance!
RE: How Can I do this in Excel 2016?
Dik
RE: How Can I do this in Excel 2016?
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!
RE: How Can I do this in Excel 2016?
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?
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!
RE: How Can I do this in Excel 2016?
> 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?
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,
for a NUance!
RE: How Can I do this in Excel 2016?
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?
Skip,
Just traded in my OLD subtlety...
for a NUance!