How do I add values in column B if column A contains the same employee surname more than once?

Question

State of Data: I have column A which has names of employes and column B has number of hours. How do I add the hours if column A contains same employee surname more than once?

I have been using what I expected to have worked, the following Formula im using should work, but I dont understand why it isnt?

Formula: =SUMIF($A$2:$B$79,A2,$B$2:$B$79)

Obviously I have 79 rows worth of data, so I'll be drilling down the formula to the other cells, which is why i haven't anchored A2.

So what am I doing wrong here?, is there a better more efficient way of calculating this?

Added Info: for example if I have an employee in column A who goes by the name of Richard, his name has appeared in column A 3 times values are in column Bas 3,4,9 So I would like to have a total in column C with Richards name in column D for example stating that he has done a total of 16 hours.

Solution

The way I understand your description of the problem, you have a list of employee names in column A ("Name") and a list of hours for each employee in column B ("HoursWorked"). Some employees are listed multiple times. You want to get a list of each employee once along with their total number hours.

The easiest way to do this would be a pivot table:

• Select the table with your data in it
• Choose Insert → Pivot Table. Choose where to put the table.
• Drag "Name" into the row label box and drag "HoursWorked" into the values box (it should default to "Sum of HoursWorked").

If you can't use a pivot table, if you can hard-code the employee names then you can use SUMIFS in this manner:

• Copy column A to column D, select column D and use Data → Remove Duplicates to get a list of unique employee names

• In column E, add the formula:

  Cell E2: =SUMIFS($B2:$B79,$A2:$A79,D2)
           (fill down to the last employee name)
  

  This means "add up everything in column B, if the corresponding cell in column A equals the value in D2".

Comments

[Stephen Jennings]

I'm not sure I fully understand what you're trying to do. Are you trying to come up with a total number of hours for each employee? Or something else?

[Stephen Jennings]

My mistake, I repeated the first argument. Edited.

[I AM L]

Thank you so much @StephenJennings, although I Anchored both column letter and number =CountIFS($A$2:$A$79,A2), to not get different values when I drill down, But i'm getting all zeros for part2?

[Stephen Jennings]

Okay, I revised my answer. I was misunderstanding what you wanted.

[glh]

Same question stackoverflow.com/questions/16161608/…

[I AM L]

I have add more information to my question.

[I AM L]

I tried to do the PivotTable version before, but I kept getting all zeros when i do a "Sum", Im also getting all zero values for column E, it makes sense that it should work, I have no idea why im getting all zeros, the formatting is set to "General" so I dont know what I'm doing wrong..

[Stephen Jennings]

I think your numbers are actually text, even though the formatting is "General". You'll probably have to play with it until you get it right. Try setting the formatting to "Number" then retyping the values.

[I AM L]

Ur correct, they are text, even though they are set to "General" for some reason Im getting an error exclamation sign stating that its still in text and it needs to be converted to "number", its unbelievable, I have fixed it now and Im getting correct values, but its really frustrating how its set as "Number" but its not actually set as "Number"! Thanks Stephen for everything!

[Stephen Jennings]

You're welcome, glad you got what you needed.