Task totals across multiple plans & within a set timeframe

melissalk

Hello!
My formula skills are pretty basic so I feel like this might be a bit more than that.

I am trying to calculate the number of tasks for four departments across multiple project plan sheets. I want to look at a timeframe scale of the last 3 months and the next 6 months.

The four departments are: Editorial, Design, Production, Quality-Copyedit.

My end goal is to show this as a line graph on a dashboard so we can see peak times for each department during the timeframe. I started to create a metrics sheet to set up references and pull the data but I'm stuck on the figuring out the formula to look at that date range.

The image below is from our project plan template:

And the graph that started this whole idea is from the Data Input & Data Collection template set. This is what we're trying to accomplish but not an average; we want actual task count. I've tried using the sheet this pulls from as a guide, but I'm not having the best luck.

Any help is much appreciated! :-)

Thank you,
Melissa

jmyzk_cloudsmart_jp

Hi @melissalk

You must create multiple metrics sheets to use the same formula structure, each summing task numbers of corresponding Project Tasks sheets, as the range references like {Task Name}, {Task Type}, and {Actual Start Date} refer to a specific sheet, if you are using column formula, which is more robust. (Alternatively, you can use the cell formula to get the task numbers in a sheet, as shown below.)

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

(It is a good custom to use {sheet name: column name} combination like, {Sheet1: Task Name}, {Sheet 2: Task Name)

In my copied demo dashboard, I used the first method.

I created a folder to hold the project task sheet and metric sheet. Then, copied the folder to create sheets with the same formula and cross-sheet reference structure.

Then, I created a report to summarize the two metric sheets.

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

The report's data is shown at the bottom in the demo dashboard.

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

If you are going to start creating multiple sheets for multiple projects, we recommend using the method just described. This is because it is time-consuming to create cross-sheet references.
If you already have multiple sheets in your project, you will need to manually create references for the number of sheets multiplied by the number of references. This is the same in the case of a single metric sheet or multiple sheets combined in a report, but multiple sheets are easier to manage.

jmyzk_cloudsmart_jp

Happy to help!😁

If you have any questions, please reach out to me by tagging me.

jmyzk_cloudsmart_jp

Hi @melissalk

Glad to hear that you figured it out.

I created a solution that automatically shows the task counts by department for the last 3 months, and it dynamically updates each month without any manual editing.

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

How it works:

🔹 Summary Fields (used to define date ranges for each month):

These are created in the summary section of the sheet to act as reusable date boundaries:

• 3 Month Ago Start
  =DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1)
• 2 Month Ago Start
  =DATE(YEAR(TODAY()), MONTH(TODAY()) - 2, 1)
• 1 Month Ago Start
  =DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1)
• 3 Month Ago End
  =[2 Month Ago Start]# - 1
• 2 Month Ago End
  =[1 Month Ago Start]# - 1
• 1 Month Ago End
  =DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

These date fields automatically calculate the first and last day of the past 3 months (accounting for different month lengths, including February).

Department-Level Formula (in the sheet)

Each department row uses a formula like this to calculate the number of tasks that overlap with the target month:

[3 Month Ago] =
IF(Department@row = "Year / Month",
YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, DAY(TODAY()))) + " / " +
INDEX({12 months of the year : Month}, MOD(MONTH(TODAY()) + 12 - 4, 12) + 1),
COUNTIFS({Task Name}, ISTEXT(@cell),
{Task Type}, "Task",
{Actual Start Date}, <=[3 Month Ago End]#,
{Actual End Date}, >=[3 Month Ago Start]#,
{Department}, Department@row)
)

• If the Department value is "Year / Month", the cell returns a label (like "2024 / December") to show at the top row for charting.
• Otherwise, it returns the count of tasks that:
  • Are text-based (i.e. actually exist)
  • Are of type "Task"
  • Overlap with the date range for that month
  • Belong to the current department

The same logic is applied to [2 Month Ago] and [1 Month Ago], using the corresponding start and end fields.

{12 months of the year : Month}

To display in a dashboard:

• The top row contains "Year / Month" in the Department column, which outputs the label for each of the 3 months (e.g., "2024 / December", "2024 / January").
• The rest of the rows are department names (e.g., "Editorial", "Design") and show task counts per month.
• This structure can be used directly in a line or column chart.

Let me know if you'd like a copy of my template — happy to share!

jmyzk_cloudsmart_jp

Hi @melissalk

You can create a metric sheet to count the number of tasks by department and month with formulas like these;

[January] =COUNTIFS({Task Name}, ISTEXT(@cell), {Task Type}, "Task", {Actual Start Date}, <=DATE(2023, 1, 31), {Actual End Date}, >=DATE(2023, 1, 1), {Department}, Department@row)

[February] =COUNTIFS({Task Name}, ISTEXT(@cell), {Task Type}, "Task", {Actual Start Date}, <=DATE(2023, 2, 28), {Actual End Date}, >=DATE(2023, 2, 1), {Department}, Department@row)

Note:

1. ISTEXT(@cell) condion can be NOT(ISBLANK(@cell)).
2. To determine if a task belongs to a month, for example, February 2023, you can use this condition;
   1. {Actual Start Date}, <=DATE(2023, 2, 28), {Actual End Date}, >=DATE(2023, 2, 1)
   2. The avove is an AND condition that works for a task spanning over a month.
   3. For example, if a task starts in January and ends in March, the task is counted as a February task.

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

melissalk

@jmyzk_cloudsmart_jp There is no way I would've put that together so I truly thank you for this! :-)
I will test this today. Since I want to count tasks (and also deliverables) across multiple schedule sheets, will that just expand the formula so that I capture all the cross sheet references?

jmyzk_cloudsmart_jp

Hi @melissalk

You must create multiple metrics sheets to use the same formula structure, each summing task numbers of corresponding Project Tasks sheets, as the range references like {Task Name}, {Task Type}, and {Actual Start Date} refer to a specific sheet, if you are using column formula, which is more robust. (Alternatively, you can use the cell formula to get the task numbers in a sheet, as shown below.)

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

(It is a good custom to use {sheet name: column name} combination like, {Sheet1: Task Name}, {Sheet 2: Task Name)

In my copied demo dashboard, I used the first method.

I created a folder to hold the project task sheet and metric sheet. Then, copied the folder to create sheets with the same formula and cross-sheet reference structure.

Then, I created a report to summarize the two metric sheets.

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

The report's data is shown at the bottom in the demo dashboard.

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

If you are going to start creating multiple sheets for multiple projects, we recommend using the method just described. This is because it is time-consuming to create cross-sheet references.
If you already have multiple sheets in your project, you will need to manually create references for the number of sheets multiplied by the number of references. This is the same in the case of a single metric sheet or multiple sheets combined in a report, but multiple sheets are easier to manage.

melissalk

@jmyzk_cloudsmart_jp Ahhh, I see. Yes, most of our projects typically have multiple schedules (project plans). I will have to set aside some time work this up. Your help and screenshots are greatly appreciated!

jmyzk_cloudsmart_jp

Happy to help!😁

If you have any questions, please reach out to me by tagging me.

melissalk

@jmyzk_cloudsmart_jp Many thanks to you, I had success today in putting this together based on 3 projects we currently have in the works. :-) I was able to add on to the formula so I could count Deliverables and Tasks.

I created a metric sheet for each project and pulled them into the report as you shared above. End result is this wonderful line chart:

I'll adjust what month columns I summarize as we want to look at 3 months back and 6 months forward. This did create a question…for projects that span over a second year, do I just need to add that to the formula above so that it pulls in that data?

I can't thank you enough for your assistance on this. You saved me much time and a few headaches. :-)

-Melissa

melissalk

@jmyzk_cloudsmart_jp I think I figured out my year question - I created month columns to capture the months of the additional year and adjusted the formula. It seemed to work. :-)

jmyzk_cloudsmart_jp

Hi @melissalk

Glad to hear that you figured it out.

I created a solution that automatically shows the task counts by department for the last 3 months, and it dynamically updates each month without any manual editing.

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

How it works:

🔹 Summary Fields (used to define date ranges for each month):

These are created in the summary section of the sheet to act as reusable date boundaries:

• 3 Month Ago Start
  =DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1)
• 2 Month Ago Start
  =DATE(YEAR(TODAY()), MONTH(TODAY()) - 2, 1)
• 1 Month Ago Start
  =DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1)
• 3 Month Ago End
  =[2 Month Ago Start]# - 1
• 2 Month Ago End
  =[1 Month Ago Start]# - 1
• 1 Month Ago End
  =DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

These date fields automatically calculate the first and last day of the past 3 months (accounting for different month lengths, including February).

Department-Level Formula (in the sheet)

Each department row uses a formula like this to calculate the number of tasks that overlap with the target month:

[3 Month Ago] =
IF(Department@row = "Year / Month",
YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, DAY(TODAY()))) + " / " +
INDEX({12 months of the year : Month}, MOD(MONTH(TODAY()) + 12 - 4, 12) + 1),
COUNTIFS({Task Name}, ISTEXT(@cell),
{Task Type}, "Task",
{Actual Start Date}, <=[3 Month Ago End]#,
{Actual End Date}, >=[3 Month Ago Start]#,
{Department}, Department@row)
)

• If the Department value is "Year / Month", the cell returns a label (like "2024 / December") to show at the top row for charting.
• Otherwise, it returns the count of tasks that:
  • Are text-based (i.e. actually exist)
  • Are of type "Task"
  • Overlap with the date range for that month
  • Belong to the current department

The same logic is applied to [2 Month Ago] and [1 Month Ago], using the corresponding start and end fields.

{12 months of the year : Month}

To display in a dashboard:

• The top row contains "Year / Month" in the Department column, which outputs the label for each of the 3 months (e.g., "2024 / December", "2024 / January").
• The rest of the rows are department names (e.g., "Editorial", "Design") and show task counts per month.
• This structure can be used directly in a line or column chart.

Let me know if you'd like a copy of my template — happy to share!

melissalk

Hello @jmyzk_cloudsmart_jp! This is beyond incredible! I just knew sheet summary could somehow play into this, but I couldn't make the connection of how to properly use it. If you are willing to share the template, I would be so very grateful. My email is melissak@trilliumpublishing.com.