Best Of

Hi @rcfjcc

Thank you for sharing the Excel file.

Here is a demo solution.

https://app.smartsheet.com/b/publish?EQBCT=c169314144c34303816f92691e1bbca2

For the solution, I created a metric sheet that counts the number os tasks by submitter during a specific period.

As the image shows, I used the Sheet Summary to designate the period.

https://app.smartsheet.com/b/publish?EQBCT=88fdc04f6c964f8d8ee5c3157cb63e91 (The metric Sheet at the bottom of the dashboard)

As I wanted to populate the submitters dynamically, I added column numbers from 1 to 10 in the # column.

Then, using the INDEX(DISTINCT()) function, I listed the submitters' emails.

[Submittter] =IFERROR(INDEX(DISTINCT({LearningDeployment_Julie : Submitter}), [#]@row), "")

[Number of tasks] =IF(ISBLANK(Submittter@row), "", COUNTIFS({LearningDeployment_Julie : Name}, ISTEXT(@cell), {LearningDeployment_Julie : Submitter}, Submittter@row, {LearningDeployment_Julie : not Parent}, true, {LearningDeployment_Julie : Date}, <=End#, {LearningDeployment_Julie : End Date}, >=Start#))

[Rank] =IF(ISNUMBER([Number of tasks]@row), RANKEQ([Number of tasks]@row, [Number of tasks]:[Number of tasks], 0))
[Top 3] =IF(ISNUMBER(Rank@row), RANKEQ(Rank@row, Rank:Rank, 1) <= 3)

To prevent counting the parent's task, I added the [not Parent] column, as shown below. If you want to include the parent task as well, remove the bold part.

COUNTIFS({LearningDeployment_Julie : Name}, ISTEXT(@cell), {LearningDeployment_Julie : Submitter}, Submittter@row, {LearningDeployment_Julie : not Parent}, true, {LearningDeployment_Julie : Date}, <=End#, {LearningDeployment_Julie : End Date}, >=Start#))

The conditions that compare the Date and End Date with the Summary Fields get the number of tasks in the period.

https://app.smartsheet.com/b/publish?EQBCT=9a1ff0b50af3468d93c4e70ab700d776 (You can access the published sheet and apply the filter to check if the formula is working correctly.)

By the way, the APAC Smartsheet Aligned Bootcamp has been held at the Marriott Hotel since the year before last. I stayed at the first one in Bangkok, and the bed was the best I have ever had.😊