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.