How to write an Excel Formula that takes a total across multiple repeating rows

I'm working with payroll data in a spreadsheet. What I'm looking to find is a total across different rows of employees with different roles. For example, how could I write a forumla for the "Total Hours" column below?

``````╔═══════╦══════════╦═══════════╦═══════╦═════════════╗
║ Emp # ║ Employee ║   Role    ║ Hours ║ Total Hours ║
╠═══════╬══════════╬═══════════╬═══════╬═════════════╣
║     1 ║ Jim      ║ Role 1    ║     5 ║           7 ║
║     1 ║ Jim      ║ Role 2    ║     2 ║             ║
║     2 ║ Bob      ║ Only Role ║    10 ║          10 ║
║     3 ║ Jane     ║ Role 1    ║    10 ║          15 ║
║     3 ║ Jane     ║ Role 2    ║     2 ║             ║
║     3 ║ Jane     ║ Role 3    ║     3 ║             ║
╚═══════╩══════════╩═══════════╩═══════╩═════════════╝
``````

There are many ways to do this. Here are three:

1. Use a pivot table:

1. Use Data>Outline>Subtotal

1. Use UNIQUE and SUMIF:

This will get the unique list of employees from Table1 (the blue table shown above):

``````=UNIQUE(Table1[[Emp '#]:[Employee]])
``````

Put this against each row to get the sum of the hours:

``````=SUMIF(Table1[Emp '#],A11,Table1[Hours])
``````

FlexYourData
• 6,430
• 2
• 6
• 21

Another possibility is to use the formula:

``````=IF(A2=A1,"",SUMIF(A2:A7,A2,D2:D7))
``````

It performs a check of the Emp# and adds hours if the preceding Emp# is different