I have a spreadsheet with employee names and hours worked. Each row has a name and unique file number, department number, temporary department number, and then hours worked, vacation and so on.

I want to combine rows with duplicate employee names and sum the hours.

I have tried pivot tables and consolidate functions and they don't quite display it the way I'd like.

I tried consolidate first and it did sum the hours but it also summed their department number and temporary department number. Also, if I used their file number as the identifier, the name did not appear next to it. If I put the name in the leftmost column, then the it also summed the file number.

I tried the pivot table next. It worked except... I could never figure out how to get all the data to display each in its own cell like the original spreadsheet. It keep wanting to stack the department numbers under the name instead of in a cell next to it. And when I tried moving it to values, then it wanted to sum the dept numbers. I spent a LOT of time rearranging the pivot table and reformatting each field value setting. When I selected the data initially and clicked insert pivot table, it took all the data and stacked it on the left in a unusable format. I spent ages rearranging all the fields and changing the view... I don't know if a pivot would work. If someone does suggest a pivot, I need a little help with setting it up, what goes where.(in regards to the 4 boxes, drag and drop, you know, row labels, column... etc) and with the value settings.

But maybe there is a more simple way.

My goal is to combine the names and sum the hours while keeping the file number and department numbers in tact and in the same format more or less.

I can't attach the file, but this is how it's set up... the columns are titled like this starting at column A: File#; Name; Home Department; Temp Department; Reg Hours; OT Hours; Sick hour; Vacation hours; Straight OT;

And there are multiple entries with duplicate names

my spreadsheet

  • 1
    You would get better support if you showed what result you want. – Scott Jul 14 '18 at 0:25
  • The OP hasn’t logged in since June 29 (2½ weeks ago).  I consider this question to be abandoned. – Scott Jul 16 '18 at 6:02

For this to work, you will need another table with a complete employee list:

Using the following formula:


more descriptively:

=SUMIF(<Employee name range>,<employee name>,<column to sum>)

I used a simplified version of your table:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.