Formula to calculate total for each unique value

I have an excel sheet which has two columns- Employee ID and Number of hours worked on project. Employee IDs and Number of hours worked go like this-

ID Hours worked on project

101 .76
101 .33
102 .97
103 .77
103 .54
103 .65
103 .66
104 .87
104 .64

This goes on for 1830 ID values


I need a formula to calculate the total number of hours worked by each employee, for example Employee 101 has worked a total of 1.09 hours. Employee 102 has worked .97 hours. Employee 103 has worked 2.62 hours and so on for each employee. Each employee ID occurs multiple times (in many cases) because each employee works on several projects at the same time.

Please give me a formula, thank you very much for the help!

Thanks for changing your thread title.

With your Data sorted on Column A (ID)
in C2

Please Login or Register  to view this content.

Drag/Fill Down

Filter column C for non-blanks.

If, for some reason, you are not allowed to sort column A then you will need to use a lookup list of unique IDs

Hope this helps

Thank you very much. Worked superbly.

Now, how do I filter the values by ID, so that the total for each ID is shown only once?

Each ID occurs randomly throughout the spreadsheet.

For example,

ID No. of hours
101 2.3
101 2.3
102 4
104 6.8
101 2.3
102 4
103 7
101 2.3

Please suggest a formula for each ID to be displayed just once, and in the right order like this:

ID No. of hours
101 2.3
102 4
103 7
104 6.8

I also need to copy all this data to another spreadsheet where duplicate IDs have been eliminated, but ID numbers occur randomly like this:

ID No.of hours
103 7
101 2.3
102 4
186 11
479 3
104 6.8


Thank you very much, and sorry to trouble!

Re: Part One

Why not just use a Pivot Table ? Set "ID" as Row Label and "Hours Worked on Project" as Data Field set to SUM.
Job done - 10 secs (max)

Re: Job Two

Will depend on Part One

As I said earlier the formula will only give a clean result if your data is sorted by Column A (ID)

as you already have a lookup list of unique values in another sheet then you can use

Please Login or Register  to view this content.

Adjusted to suit.

DonkeyOtes suggestion to use a pivot table does all this for you

I have put all the alternative solutions in this demo workbook