Using a formula to count numbers of projects assigned to an employee and at what stage its at.

Galadriel975

Hello!

I've created a sheet to pull metrics from for some charts I need to make and I'm trying to create a formula that counts the number of projects that are assigned to each employee and its progress level.

I more or less understand the first part of the formula counting the assigned projects, but I'm stuck on how to get it to limit the criteria based on information in my primary sheet.

=COUNTIFS({Contact Column}, FIND("address@address.com", @cell) >= 0)

The fields I need it to use to limit the counts are all check-boxes.

Let me know if you need anymore information.

Thanks!

Paul Newcome

It depends on how your contacts are stored in the source sheet. If your column is set up as a contact type column, it is possible they are displayed as "First Last" in which case you would use the cell reference in the COUNTIFS above (@cell = [Column2]$1).

If they are only stored as name@address.com, then you would need to either put the email address in the sheet containing the formula similar to how you have the names and reference that.

If it could be either email or name in the source sheet, you would put both in the sheet with the COUNTIFS and use an OR function. The below would be under the assumption that names are on row 1 and emails are on row 2.

=COUNTIFS({Source Sheet Assigned To Column}, OR(HAS(@cell, [Column2]$1), HAS(@cell, [Column2]$2)), {Source Sheet Helper Column}, @cell = $[Primary Column]@row)

Paul Newcome

Are you able to provide a sample of the source data?

ShelbyWarren

Try putting this in each empty cell in the sheet you screenshotted above, and create the cross-sheet references:

=COUNTIFS({Status}, $[Primary Column]@row, {Contact}, [Column 2]$1)

Paul Newcome

@Shelby Warren That would normally work, but the OP says "The fields I need it to use to limit the counts are all check-boxes." I am thinking there will need to be a helper column on the source sheet with a nested IF to output the status based on the checkboxes in each row and then the COUNTIFS evaluating the helper column as the status column would work, but I wanted to see the existing source data as it is to confirm.

Galadriel975

https://community.smartsheet.com/discussion/comment/373606#Comment_373606

These check-boxes move the project from one report to another within the workapp and are how we tell where in the process the project is currently at.

What I want the formula to tell me is how many projects Lena has in each of these groups (minus the MY GMs grouping). Basically I want a project that has been assigned to Lena but doesn't have the TO's Uploaded to SAP box cheched to count under her "in progress" projects, while one with that box checked to count towards the "awaiting tracking" projects.

Does that help? @Paul Newcome

Paul Newcome

Ok. In that case I definitely suggest a helper column on the source sheet that uses a nested IF to output the status on each row based on the checkboxes.

Then you would be able to use a much more simplified COUNTIFS looking a the helper column to pull in your counts.

Galadriel975

https://community.smartsheet.com/discussion/comment/373706#Comment_373706

I'm not entirely sure what you mean on this, I'm so sorry for my confusion. I'm just learning formulas now, so I'm not used to the nuances yet.

Paul Newcome

No worries.

In the source sheet, you can insert a text/number column (called "Helper" in this example) with a nested IF that will output the status based on which boxes are checked. If you can provide the logic for boxes checked and the corresponding output, I can put together something more specific, but the general idea would be:

=IF([Complete Box]@row = 1, "Complete", IF([In Progress Box]@row = 1, "In Progress", IF([Not Started Box]@row = 1, "Not Started")))

Then your COUNTIFS in the second sheet would look something along the lines of:

=COUNTIFS({Source Sheet Assigned To Column}, @cell = [Column2]$1, {Source Sheet Helper Column}, @cell = $[Primary Column]@row)

Galadriel975

@Paul Newcome That explanation on the helper column helped a lot and I got it functional within the primary sheet.

My only question about the COUNTIFS formula you provided is how I would incorporate the email of the assigned employee so that the results are filtered and it knows what to pull from the helper column?

If my understanding is correct then somewhere in the formula there would have to be an "address@address.com" and (if I'm filtering only those that are complete) "Complete". I could be way off base though.

Paul Newcome

It depends on how your contacts are stored in the source sheet. If your column is set up as a contact type column, it is possible they are displayed as "First Last" in which case you would use the cell reference in the COUNTIFS above (@cell = [Column2]$1).

If they are only stored as name@address.com, then you would need to either put the email address in the sheet containing the formula similar to how you have the names and reference that.

If it could be either email or name in the source sheet, you would put both in the sheet with the COUNTIFS and use an OR function. The below would be under the assumption that names are on row 1 and emails are on row 2.

=COUNTIFS({Source Sheet Assigned To Column}, OR(HAS(@cell, [Column2]$1), HAS(@cell, [Column2]$2)), {Source Sheet Helper Column}, @cell = $[Primary Column]@row)