Reporting and dashboard help

LauraM

I have a smartsheet containing work experience for every person in our company. The rows go something like this...

• Steve Smith | Project Manager | Company A | 2.5 years | etc...
• Steve Smith | Project Manager | Company B | 1.5 years | etc...
• Steve Smith | Project Coordinator | Company B | 5 years | etc...
• Karen Banks | Business Analyst | Company C | 1.75 years | etc...
• Karen Banks | Business Systems Analyst | Company D | 2 years | etc...
• Karen Banks | Project Manager | Company E | 1 year | etc...

I want to get to the following:

For each employee, how many years of experience do they have for a particular job title (i.e. Steve Smith has 4 years as a Project Manager). 

Also...

For all employees, how many years of experience do we have for a particular job title (i.e. Steve Smith has 4 years as a Project Manager and Karen Banks has 1 year - so we have 5 years total).

I've tried looking at a report and a dashboard and haven't been able to figure this out. 

Thanks!

Laura

 

Jim Hook

Hi Laura,

This is fairly easy to achieve as long as you setup the rows of employee experience correctly. By that I mean that you have consistent people names and job titles for each entry (all spelled exactly the same), and a column next to the job description with the years of service in that role. After that it's a matter of setting up a number of SUMIFS formulas that scan the table for the desired combinations of criteria to sum. For just Steve Smith's years as a project manager, for example, you would sum years where Steve is the person and project manager is the position. To find the total project management experience for all you just sum all the years in rows that match project management. Very straightforward after you get the hang of SUMIFS function.

Jim