Best Of

TECHNICALLY you can using a SUM and VALUE functions, but it will still require quite a few helper columns as you would have to first wrap each parsing formula in a VALUE function then nest a number of these inside of a SUM function. This provides for a very long and hard to manage formula that will end up hitting limitations very quickly (which is where the helper columns column in). You will also need to wrap each parsing formula in an IFERROR. It would end up looking something like this…

=SUM(VALUE(IFERROR(MID(1st_parsing_solution), 0)), VALUE(IFERROR(MID(2nd_parsing_solution), 0)), VALUE(IFERROR(MID(3rd_parsing_solution), 0)))

As you can see, putting in the actual "guts" for each of the parsing formulas will extend this quite rapidly, and formulas cannot contain more than 4,000 characters including spaces. This means you would need to break this down across multiple helper columns to add up (for example) parts 1 - 15, 16 - 30, 31 - 45, etc..

Then once you do all of those formulas, you would add together all of your helper columns.

@jmyzk_cloudsmart_jp I wish I could take credit for the SUBSTITUTE thing. I can't remember who I originally got this from some years ago, but I'm not the original creator for that method.

Happy to help!😁

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

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.