Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 Apr 23 '13 at 6:18
    
Hi @StephenJennings your answer is correct, except Im having trouble with the first formula, as its stating that it has "too few arguments" –  I AM L Apr 23 '13 at 6:19
    
My mistake, I repeated the first argument. Edited. –  Stephen Jennings Apr 23 '13 at 6:22
    
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? –  I AM L Apr 23 '13 at 6:25
    
Okay, I revised my answer. I was misunderstanding what you wanted. –  Stephen Jennings Apr 23 '13 at 6:39

1 Answer 1

up vote 5 down vote accepted

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".

share|improve this answer
    
I have add more information to my question. –  I AM L Apr 23 '13 at 6:32
    
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.. –  I AM L Apr 23 '13 at 6:45
    
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. –  Stephen Jennings Apr 23 '13 at 6:57
    
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! –  I AM L Apr 23 '13 at 7:07
    
You're welcome, glad you got what you needed. –  Stephen Jennings Apr 23 '13 at 7:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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