How to attribute work completed to an employee

Katie J · 12/30/21

I am trying to find the correct data type and formula to connect complete work (that is reported via form) to the employee who completed the work - in the sheet summary.

I'm looking for daily completion per employee, as well as options for weekly, monthly, and all historical in a data roll up. Once I get the formula the findings will then be displayed on a dashboard for progress reports.

I have tried using a contact list, dropdown list, and text/number fields to gather the employee's name. Then tried CountIF and CountIFS formulas to calculate and it doesn't seem to be working. The work completed data is coming from a dropdown list with radio buttons "Attempted" vs. "Completed"

I just get #Incorrect Argument Set for the formulas below

Example 1: Contacts with CountIF: =COUNTIFS([Outcome1]:[Outcome20], "Completed", [Navigator Name]:[Navigator Name], "Name")

Example 2: Text/number with CountIF: =COUNTIF([Navigator Name]:[Navigator Name], "Name", [Outcome1]:[Outcome20], "Completed")

If I use the Count formula it doesn't seem to recognizes the employees name and counts the number of submissions by that one employee...

CountM formula does recognize the number of submissions by the entire team but isn't helpful.

Any guidance is welcome...

Genevieve P. · 12/31/21

Hi @Katie J

My apologies, I completely missed the row reference in the COUNTIF formula!

You did it correctly, but try using @row instead of 4 or a number so that you can make it a Column Formula because it uses @row, like so:

=COUNTIF([Outcome1]@row:[Outcome20]@row, "Completed")

Then the issue in your SUMIF formula is that you've added criteria at the end. A SUMIF structure is the following:

=SUMIF([Column with Criteria]:[Column with Criteria], "Criteria", [Column to Sum]:[Column to Sum])

The Column to Sum shouldn't have any criteria. In your case, try it without the "≥0" at the end:

=SUMIF(NavName:NavName, "Chelsea lastname", SumComplete:SumComplete)

This will SUM together your SumComplete column based on the name you've specified in the NavName column. If the SUM is 0, it will display 0.

Cheers! And Happy New Year!

Genevieve

Genevieve P. · 12/31/21

Hi @Katie J

It looks like your range for the "Completed" value spans across 20 columns, is that correct?

If so, I would first use a COUNTIF per row to count the number of "Completed" values in a new, helper column, like so:

=COUNTIF([Outcome1]:[Outcome20], "Completed")

Then once you have a number per-row, you can use a SUMIF Function to Sum together the numbers in that column per employee in your summary fields:

=SUMIF([Navigator Name]:[Navigator Name], "Name", [Helper Column]:[Helper Column])

Let me know if this makes sense! If I've misunderstood your sheet set-up, it would be helpful to see a screen capture of the sheet, but please block out sensitive data.

Cheers,

Genevieve

Katie J · 12/31/21

Hey Genevieve,

Thank you for replying! I have attempted what you said to do. I created a helper column (SumComplete) but due to the formula I'm using (see below) I cannot make it a column formula, I believe if I just lock the column it will guard the cell formulas correct?

Helper Column Formula: =COUNTIF([Outcome1]4:[Outcome20]4, "Completed")

The formula is counting the number of completed task items in the row. If I sum it by all rows I'm not sure if it would then parse it back out to each employee, but correct me if I'm wrong.

I am still getting #INCORRECT ARGUMENT SET with the helper column and SUMIF formulas when applying the Employee's name to the formula.

Employee Specific Completion Formula: =SUMIF(NavName:NavName, "Chelsea {LAST NAME}", SumComplete:SumComplete, "≥0")

I have confirmed name spelling and in a COUNTIF formula the names are recognized. Additionally, if 0 are completed I would need that counted and displayed as well.

I have included a screen shot of a portion of the sheet as it is too large to capture the entire set up. I have redacted sensitive information.

Thanks again for taking a look!

Daily Work Log - Attributing Work to the Employee.png

Genevieve P. · 12/31/21