Report for Overdue Tasks for multiple Team members

TeeM

• I have individual team members’ reports in Smartsheet that have conditional formatting applied if the tasks are overdue.
• I need to count the overdue tasks for each person on a separate sheet called Metrics. I will then show that data on a management dashboard.
• My challenge is how to write the formula.

KPH

What in your sheet identifies the task as overdue? Whatever criteria it is, that you use in conditional formatting, you can use in a COUNTIFS formula. You will need to set up cross sheet references to do this on your metrics sheet.

https://help.smartsheet.com/function/countifs

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

TeeM

@KPH

To explain further...

I have several source sheets from which the Team members' tasks draw to populate their own task reports. I have set up a separate sheet called 'Metrics' where I have listed all the team members. The conditional formatting that is on the source sheets is

I am not sure what the COUNTIF and cross reference formula would/should look like.

I'm a bit out of my depth here LOL.

KPH

I wonder if a report might be better for you than a sheet with formula in. You can select all the individual sheets and create one report based on those and can embed reports on a dashboard. Take a look at:

https://help.smartsheet.com/learning-track/smartsheet-intermediate/reports

If you want to use COUNTIFS formula, here are some comments to help ...

Getting started with COUNTIFS

The syntax for a COUNTIFS function is a series of ranges and criteria, like this:

=COUNTIFS(range1,criterion1,range2​,criterion2​)

In your first conditional formatting, you have specified the range % Complete and the criteria less than 1. So you can use this logic in the formula. Starting with

=COUNTIFS([% Complete]:[% Complete],<1)

This will count all the rows in the sheet where % Complete is less than 1.

Using cross sheet references

To put this formula in a different sheet from the data (such as your metrics sheet) you need to use a cross sheet reference to replace the column reference.

Instead of the part shown in bold

=COUNTIFS([% Complete]:[% Complete],<1)

You can set up a cross sheet references to that column in your individual sheet. It will look like this:

=COUNTIFS({Team member % Complete},<1)

You cannot just copy this, you need to set up the references. The help guide will take your through how to do so.

Adding dates to COUNTIFS

You can then add other ranges and criteria to the COUNTIFS. The next one in your case is End Date is in the next 2 days.

You need to use the TODAY function here. TODAY(2) means today plus 2 days. This will count rows where the End Date is less than or equal to today plus 2 days.

=COUNTIFS({Team member % Complete},<1, {team member End Date},<=TODAY(2))

This will count all rows where the end date is in the past or in the next 2 days. If you want to count only rows where the End Date is today or in the next two days (and not in the past), you add another range and criteria to specify that the end date must also be on or after today. Like this:

=COUNTIFS({Team member % Complete},<1, {team member End Date},<=TODAY(2),{team member End Date},>=TODAY())

Team members

How you assign these counts to team members will depend on how the team member sheets are set up. If you have one sheet per member, then you will have one cross sheet reference per team member. If the team member is a name in a column on your sheet, then you can include the team member name in the COUNTIFS.

=COUNTIFS({Team member % Complete},<1, {team member End Date},<=TODAY(2),{team member End Date},>=TODAY(),{team member Name},"Burt")

If you also include the name in the metrics sheet in a column next to your formula, you can refer to it so that you don't have to edit the formula for each person:

=COUNTIFS({Team member % Complete},<1, {team member End Date},<=TODAY(2),{team member End Date},>=TODAY(),{team member Name},name@row)

Other logic

The other two logics you use are simplified versions of the ones above, so you should be able to adapt the formula here to count for those situations.

TeeM

Thanks @KPH

I'll give that a try.

KPH

Good luck!

TeeM

@KPH

I have set up a report for all team members and all source sheets as you suggested. However, I don't seem to be able to cross-reference the report on my Metrics sheet

KPH

I am not sure what you mean by "cross-reference the report on my Metrics sheet".

The report should remove the need for the metrics sheet. You can add filters to the report and add it to your dashboard.

TeeM

@KPH I think I am missing something here.

Where does the formula go; on which sheet/report? On the dashboard, I only want to show the numbers for each individual, not all the data.

From what I can work out, I can't add additional rows or columns to the report, independently of the source sheets.

Excuse my ignorance!

KPH

Hi

There were two methods you could use -

1. a report that you can embed on your dashboard. No formulas.
2. a metric sheet with formula in that you can also embed on your dashboard. This is made using the COUNTIFS formulas.

When you said you'd created the report, I thought that meant you had gone for option 1. You don't use formula in a report. You bring all the sheets together by selecting them, then can use the "Summarize" and "Filter" and "Group" options - these appear in the menu bar at the top to show just the data you need.

If you have done option 2, you will have a sheet with some COUNTIFS formula.

Maybe you can share what you have done?

TeeM

@KPH I think sharing is a good idea. How can I share the sheets with you?

I am not sure where you are located but happy to have a Teams/Zoom meeting if that helps. I am in Melbourne, Australia.

I appreciate your help!

KPH

I can't do a meeting, but if you can take a screen shot of what you've done, I might be able to understand where you are.

TeeM

TeeM

KPH

OK, you have a report, and those filters look good. 👍️

Now, you can use the Group function to group the row by Assigned to. This will put all the rows for each person under their name. There will be little triangles next to each name to expand and collapse the rows.

Then, you can use Summarize to add a count of Assigned to. This will place a count of rows that match your filer next to each person's name.

Next, you can add the report to your dashboard as a Report Widget. When you do this, be sure to select the option "Collapse Grouped Report". This will show the summarized data only - one row per person, and not the rows below each person.

TeeM

I get what is supposed to be happening but it doesn't seem to work....

There is not an option to group by 'Assigned to:' only 'Assigned To'. I am not sure if that is causing an issue??