Payroll Calculations using a Sortable Table

**.h2o.** · 07-12-2012, 07:45 AM

Hello there,

This is my first forum post and I was wondering if anybody could help me out. One of my requirements is that I need to have a sortable table for inserting payroll information. Eventually this data is going to be turned to a Pivot Table. However, I am running into a problem when I have to calculate the total hours (see sample screenshot). Some employees clock in and out multiple times in one day so that the number of rows dedicated to each employee may change.

I need some sort of smart formula that can calculate the grand total of each employee by reading column A and knowing when the text value changes. How can I do this using either Excel's built in functions or by using PowerPivot/DAX? Thanks in advance.

Excel Screenshot Example.jpg

**Pepe Le Mokko** · 07-12-2012, 07:47 AM

Hi and welcome
perhaps post a small sample sheet instead of a picture?

**.h2o.** · 07-12-2012, 08:01 AM

I added a small sample sheet.

**.h2o.** · 07-12-2012, 12:06 PM

After doing a little bit of research, I think I can word this question better. What I really want to do is find a specific text string in column A (employee name) and then sum the values of an unknown number of rows in column G (day hrs worked) and have those calculated totals in a separate column (week total hours column H ). The number of rows to be summed is contingent on all the items in column A having the same value.

**Pepe Le Mokko** · 07-12-2012, 02:44 PM

One possibility is using a Pivot Table ( grouped by months in the attached but you can also remove the grouping or group per year, quarter, etc..) Sample_Payroll.xlsx