Template Package and Metrics/Dashboard Chart

Amy Evans
Amy Evans ✭✭
in Formulas and Functions
Portfolio Metrics.png
Project Intake Sheet.png
Chart.png

Hello,

I'm using the Project Management Office Template set and trying to change the Portfolio Metrics' Project By Status column with new stages. However, I'm not sure how the formula is calculated in the screenshot.

I'd like to link to the Dashboard to create a bar chart (see mockup) of the number of projects each in status, and assuming the Project Intake Sheet will be a growing list of projects. I also included a screenshot of the Project Intake Sheet where the Project Statuses reside.

Your help is much appreciated.


Amy

· Share on FacebookShare on Twitter

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi Amy,

    It looks like this formula is indeed counting the number of Projects (referenced in the {Project ID} range) with the status specified in that row.

    In testing I found that if you had the same project ID listed twice and both rows had the same status, this formula would count that as 2... is this what you wanted? If not, we could add a DISTINCT function into the formula to make sure it's only counting the unique combination of ID & status once:

    =COUNT(DISTINCT(COLLECT({Project ID}, {Project Status}, $Label@row)))

    (Let me know if this makes sense or if you'd like me to go into further detail).


    As far as I can tell, everything is working fine, however you may want to play around with the different settings for the chart widget.

    Can I ask how you created this chart? It looks like the X axis isn't reflecting the values in the sheet you're showing us... did you use the same sheet for this chart? It would be helpful to see a screen capture where you open up the setting that says "Edit Data".

    If the data isn't displaying as you expect right away, you may also want to try switching rows/columns. Here are a few Help Articles that you may find useful as you continue to build out your dashboard:


    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi Amy,

    No problem!

    So the $ sign is an "Absolute Reference" locking in the column named Label into the formula. Without the $ mark, if you drag-filled this same formula over to a different column it would shift from referencing Label to referencing the column that's in the same distance from your new column.

    See: Absolute References in Formulas


    Then the @row function is a handy little row reference. If you have a formula in row one, you might have referenced the cell in the Label column as: $Label1

    Notice how the 1 is after the column name? This indicates what row the formula should be looking at in the Label column. I could have $Label3, $Label28, etc.

    However, if you replace the number with the @row function this means you don't need to worry about the numbers at all. @row says, look at that column in this row. This means you can copy/paste the formula to any row and it will always look within the row that the formula is in (without needing to read through the sheet and find the correct number). This helps with sheet performance as the formulas can calculate faster since they don't need to read through the sheet.

    See: @row function


    Hope that helps!

    Genevieve

    · Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi Amy,

    It looks like this formula is indeed counting the number of Projects (referenced in the {Project ID} range) with the status specified in that row.

    In testing I found that if you had the same project ID listed twice and both rows had the same status, this formula would count that as 2... is this what you wanted? If not, we could add a DISTINCT function into the formula to make sure it's only counting the unique combination of ID & status once:

    =COUNT(DISTINCT(COLLECT({Project ID}, {Project Status}, $Label@row)))

    (Let me know if this makes sense or if you'd like me to go into further detail).


    As far as I can tell, everything is working fine, however you may want to play around with the different settings for the chart widget.

    Can I ask how you created this chart? It looks like the X axis isn't reflecting the values in the sheet you're showing us... did you use the same sheet for this chart? It would be helpful to see a screen capture where you open up the setting that says "Edit Data".

    If the data isn't displaying as you expect right away, you may also want to try switching rows/columns. Here are a few Help Articles that you may find useful as you continue to build out your dashboard:


    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • Amy Evans
    Amy Evans ✭✭

    Thank you, Genevieve!

    Really great response to my question!

    Would you mind writing in plain English what this formula means? I'm familiar with COUNT function from Excel, but the "$Label@row" throws me a bit of a curve.

    The chart is a dummy mock-up. I was able to create a chart successfully with the correct X/Y values.

    Thank you for the article links. I'll review.

    AE

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi Amy,

    No problem!

    So the $ sign is an "Absolute Reference" locking in the column named Label into the formula. Without the $ mark, if you drag-filled this same formula over to a different column it would shift from referencing Label to referencing the column that's in the same distance from your new column.

    See: Absolute References in Formulas


    Then the @row function is a handy little row reference. If you have a formula in row one, you might have referenced the cell in the Label column as: $Label1

    Notice how the 1 is after the column name? This indicates what row the formula should be looking at in the Label column. I could have $Label3, $Label28, etc.

    However, if you replace the number with the @row function this means you don't need to worry about the numbers at all. @row says, look at that column in this row. This means you can copy/paste the formula to any row and it will always look within the row that the formula is in (without needing to read through the sheet and find the correct number). This helps with sheet performance as the formulas can calculate faster since they don't need to read through the sheet.

    See: @row function


    Hope that helps!

    Genevieve

    · Share on FacebookShare on Twitter
  • Amy Evans
    Amy Evans ✭✭

    Hello Genevieve,

    I reviewed the article references and have more questions. I hope it is okay to add to this string. If you'd like me to create a new question thread, please let me know.

    1) @Row Function

    =IF(Complete3 <> 1, IF(TODAY() - [Due Date]3 > 0, "Red", IF(TODAY() - [Due Date]3 > -3, "Yellow", "Green")))

    What does the "()" represent?


    2) Summary of Formula References

    Are you able to count up a column's sub-tasks and ignore category headings? For example, if you indent a row to show a task and want to count up all indented rows, what is the best way to do this?


    Thank you SO much.

    AE

    · Share on FacebookShare on Twitter
  • Amy Evans
    Amy Evans ✭✭

    Thanks for the helpful tutorial links and thorough answers.

    I guess I do need to brush up on my formulas! :) I'll review those tutorials.


    Amy

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    Happy to help! 🙂Please feel free to ask any more of your formula questions when you have them.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!