Counting tasks across multiple sheets

Ryan Levitz

Hi there. I have 17 separate project sheets. I have a portfolio-level dashboard and I want to include a metric that sums the total number of tasks across all of the sheets. I've been searching all over for the best way to do this. Some people have said countifs others have done roll up sheets and cell linking, etc. I do have sheet summaries set up in each of the project sheets, but it looks like I can't create formulas off of those?

I'd appreciate any guidance you can provide.

Thanks~

Paul Newcome

The basics would be that you would insert a text/number column, use a COUNTIFS formula in one of the cells to get the count you want, then hide the column from view to keep the sheet looking clean.

Once you have done this for each of your source sheets, you would then create a new sheet (Metrics Sheet) and then use cell linking to pull the count(s) in from each of the source sheet. From there you can run your SUM or AVG functions to get your overall metrics.

Depending on what metrics you need to pull from your source sheets, you could even go so far as to create a metrics sheet for each of the source sheets and then use cell linking form there.

My personal choice is to use a series of hidden columns on each of the source sheets. One column for each data point. Then I go across the top row and use basic formulas to pull my "by project" metrics.

Then I create a rollup sheet that has all of the same columns in the same order as the helper columns on my project sheets. You can then select all of those cells in a single row across all of the columns and cell link to all of the helper columns in one of your sheets in a single go instead of having to cell link a bunch of cells from a single sheet individually.

Now that I have all of my projects linked to my rollup sheet, I create a metrics sheet that pulls from my rollup.

So each of my project sheets has automated "by project" metrics being calculated on the project sheet itself:

Since I keep the same number of columns in the same order on my Rollup, I can cell link Project A all at the same time by highlighting the appropriate columns in my rollup, following the usual cell linking steps, then selecting the appropriate columns from row 1 of my "Project A" project sheet.

This makes it much more efficient if you are collecting a lot of data points on each project.

I personally then create a separate metrics sheet to show my portfolio level data, because in reality I am usually collecting about 25 different data points for each project. It also helps to keep my metrics separated from the source data (in this case the rollup sheet) so I can reference entire columns in my formulas.

Since I use a template for my Project Sheets, all I have to do is save the new project sheet, then do one quick set of cell links on my rollup sheet, and my new project is now added to all of my metrics (and in turn my dashboard(s)).

It does take a little bit of setup, but it saves a lot of time long term when you can work from a template and add new projects relatively quickly.

Paul Newcome

It is going to be up to personal preference whether you use a hidden helper column on each sheet and then cell link to a master sheet or if you add a bunch of COUNTIFS together.

Ryan Levitz

Hey Paul, it was actually one of your answers I was looking that was using the helper columns. I'm relatively new to smartsheet, and I was having a bit of trouble following along with what you were doing. Any videos or examples you could give?

Paul Newcome

The basics would be that you would insert a text/number column, use a COUNTIFS formula in one of the cells to get the count you want, then hide the column from view to keep the sheet looking clean.

Once you have done this for each of your source sheets, you would then create a new sheet (Metrics Sheet) and then use cell linking to pull the count(s) in from each of the source sheet. From there you can run your SUM or AVG functions to get your overall metrics.

Depending on what metrics you need to pull from your source sheets, you could even go so far as to create a metrics sheet for each of the source sheets and then use cell linking form there.

My personal choice is to use a series of hidden columns on each of the source sheets. One column for each data point. Then I go across the top row and use basic formulas to pull my "by project" metrics.

Then I create a rollup sheet that has all of the same columns in the same order as the helper columns on my project sheets. You can then select all of those cells in a single row across all of the columns and cell link to all of the helper columns in one of your sheets in a single go instead of having to cell link a bunch of cells from a single sheet individually.

Now that I have all of my projects linked to my rollup sheet, I create a metrics sheet that pulls from my rollup.

So each of my project sheets has automated "by project" metrics being calculated on the project sheet itself:

Since I keep the same number of columns in the same order on my Rollup, I can cell link Project A all at the same time by highlighting the appropriate columns in my rollup, following the usual cell linking steps, then selecting the appropriate columns from row 1 of my "Project A" project sheet.

This makes it much more efficient if you are collecting a lot of data points on each project.

I personally then create a separate metrics sheet to show my portfolio level data, because in reality I am usually collecting about 25 different data points for each project. It also helps to keep my metrics separated from the source data (in this case the rollup sheet) so I can reference entire columns in my formulas.

Since I use a template for my Project Sheets, all I have to do is save the new project sheet, then do one quick set of cell links on my rollup sheet, and my new project is now added to all of my metrics (and in turn my dashboard(s)).

It does take a little bit of setup, but it saves a lot of time long term when you can work from a template and add new projects relatively quickly.

Ryan Levitz

Thank you very much for that detailed breakdown. Makes sense in my head at this point. Going to work on this now.

Paul Newcome

Happy to help. 👍️

Feel free to let me know if you want some help with any of the details.

Ryan Levitz

Got it! This was exactly what I need to produce the desired data for the CEO's dashboard. Thanks again for the help!

Paul Newcome

Happy to help. 👍️