Creating a Formula

Question

Good afternoon.

I track projects and assignments in 1 sheet.

There may be 1 project that has 3 assignments.  Because Assignees are tracking indicating time spent on a project, there needs to be a separate line for each assignee - therefore the project is listed 3 times in a sheet.  When I pull metrics, currently, the 1 project may be listed 3 times (1 for each assignee)

Example:  ABC Project is assigned to A, B & C; the project's Business Unit is XYZ

I pull metrics for

Projects for each Assignee (A,B&C)

Projects (In Progress, Not Started and Completed) for each Business Unit

Can any help me try to create a formula that says if 1 project is assigned to more than 1 Assignee, only count it once AND only count the Business Unit once also?

I hope I am explaining this correctly

Thank you

Heather Duff · Answer

Ok - here's my rough solution. It's not perfect, but it may get you closer to what you're looking for. Let's assume the project name column is called Project, the Assignee column is called Assignee, time spent is called Time, business unit is Unit, and status is Status.

To count number of projects for each assignee:

=countif(assignee:assignee, "Assignee A") - where "Assignee A" would be changed for each person.

To sum time spent for each assignee:

=sumif(assignee:assignee, "Assignee A", time:time)

To count time spent for each project:

=sumif(project:project, "Project 1", time:time) - where "Project 1" would be changed for each project. name.

To sum time spent within each business unit:

=sumif(unit:unit, "Unit X", time:time) - where "Unit X" would be changed for each unit.

To count projects associated with each business unit, ruling out duplicate projects, it's tricky. I first made a column called Weight, which I later hid. The Weight column had a formula that went like this:

=1 / (COUNTIF(Project:Project, Project@row)) - this basically makes each project whose name appears twice count as 0.5, each project that shows up 3 times as 0.333, 4 times as 0.25, etc. - so that when you add them up by unit the entire project equals 1.

From there, I used this formula to "count" (sum the weight of) projects associated to the unit:

=SUMIF(Unit:Unit, Weight@row, Weight:Weight)

To count projects with a specific status within each business unit, ruling out duplicate projects, this one is also tricky. I am assuming the status of the project for one person could be listed as not started, while the same project's status for another person could be listed as in progress or complete. I actually couldn't figure out how to rule out duplicates, but here's how to count how many projects (including duplicates) are listed for each unit:

=countifs(unit:unit, "Unit X",status:status,"in progress") - where "Unit X" would be changed for each unit, and "In progress" would be changed for each status.

Hope this helps!

Best,

Heather

Kimberly Washeleski · Answer

Hi Heather

All of the information is in one sheet.  I have created numerous reports (one for each Assignee), but again, it is pulling from the same sheet.

What I have done to get around this currently is to only have the Business Unit display once for the first Assignee, but not the rest.  The spreadsheet is incomplete.

Thank you

Heather Duff · Answer

Hi Kimberly,

Where are you compiling the metrics? I've got a couple of ideas, but want to be sure I'm on the same page as you. Do you think you'll do it within the same sheet, a separate sheet, or a report?

Thanks,

Heather