3 Replies Latest reply on Jan 22, 2019 6:54 AM by Moses Dhas

# Get Count and Sum by User Defined Categories

Hi,

I got an excel spreadsheet that has employees with number of hours worked for each time period. The badge number is repeating because the employee has worked on multiple days. i would like a report that is categorized like below

0 - 2021 - 4546 - 60More Than 60Total
Hours1,04810,22477,58254,568143,422
Men1717012939092390

Head Count = Total number of unique badge numbers that worked between 0 to 20 hrs, same for 21 - 45, 46 - 60 and more than 60

Hours = Total number of hours than employees have worked between 0 to 20 hours, same for 21 - 45, 46 - 60 and more than 60

Men = Hours / 60

the 60 should be a parameter cos it can be 50 or 70 or 80 based on the requirement.

attached is the excel file. sheet Summary, rows 41 to 45 is the output data.i would like the above table to be calculated only from the Yard man-hours sheet.

thanks for any assistance that can be provided.

• ###### 1. Re: Get Count and Sum by User Defined Categories

hi Moses,

I'm pretty sure we can do this, but got a couple of questions ...

What do you mean by "each period of time"? Do we sum up the hours over an entire employee (for all days), to put them in the "hours worked" bucket, or is it "hours worked over a week (or other timeframe)"?

You say you want the 60 Parameterised and could go down to 50? what happens to people in the 46-60 bucket if the last group is 50+?

If you let me know I'll have a stab!

• ###### 2. Re: Get Count and Sum by User Defined Categories

Hi, Moses

Please find my solution attached as well as below screenshot of result

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 3. Re: Get Count and Sum by User Defined Categories

hi ZZ,

Awesome, thanks a lot.