0

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 ║             ║
╚═══════╩══════════╩═══════════╩═══════╩═════════════╝
Jaybone
  • 1

2 Answers2

1

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

  1. Use a pivot table:

enter image description here

  1. Use Data>Outline>Subtotal

enter image description here enter image description here

  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])

enter image description here

FlexYourData
  • 6,430
  • 2
  • 6
  • 21
0

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

IMG:

Reddy Lutonadio
  • 17,120
  • 4
  • 14
  • 35