Sign in to join the conversation:
Hello,
I am trying to create a chart from a report to have in my Dashboard. I have a report pulling my Engineer's Hours per project, and would like to create a way to sum all those up and create a graph showing this.
Any help?
It may be easier to create a Metrics sheet that sums all of that up and then use that for your Chart.
How would I go about making the Metric Sheet?
There are a few different ways. The two easiest would to be to create a grid with Engineers on one axis and Projects on the other (column heades in bold). For this example I am assuming your master sheet is setup with the Engineers listed in one column, Hours listed in a second column, and the Projects listed in a third column.
.
Engineer Project A Project B Project C
Project A Project B Project C
Engineer A
Engineer B
Engineer C
You could then use a SUMIFS formula similar to the following:
=SUMIFS({Master Sheet Sum Range}, {Master Sheet Engineer Range}, $Engineer@row, {Master Sheet Project Range}, [Project A]$1)
{Master Sheet Sum Range}: Use the appropriate steps for cross sheet referencing and select the column in your Master Sheet that contains the hours to sum.
{Master Sheet Engineer Range}: Same as above except selecting the column housing the Engineers' names.
$Engineer@row: Leave as is. References the Engineer's name in the Engineer column on your Metrics sheet for whatever row the formula is on. Note the $ to lock the column reference.
{Master Sheet Project Range}: Same as above ranges except selecting the column housing the Projects.
[Project A]$1: Leave as is. Note the $ to lock in the row reference.
When putting the formula in the first cell of the table ([Project A]2), this will allow you to dragfill the rest of the table without having to update the formula for specific data.
You could also flip the table so that the Projects are listed down the first column and the Engineers are listed across the first row.
To provide a visual reference for the example above... The first screenshot is of my Master sheet and the second is of the Metrics sheet.
You can then use the Metrics sheet to build your Chart widget in the dashboard.
Is there more info regarding this matrix formula. I am trying to use this, but have questions.
Hello, I'm working through the writing formulas optional activity and in the 'Nested IF function - Progress Column' task it directs you to put in the following formula '=IF([% Complete]@row = 0, “Empty”)'. However, when I put this formula into the spreadsheet, '04a_Marketing Request Tracker - Formulas', I get an…
I have an automation that has a condition where I am looking at the column "LEA Interco Supporting Site(s)" and trying to find columns that have the value "Fort Loramie" in them. This works well when "Fort Loramie" is the first item listed in the cell, but it doesn't work when "Fort Loramie" is the second or third value in…
Have any of you created a Smartsheet that would show the savings for a project by the month? In other words, if I saved $1200 annually and the project ended in say March, then the savings would go $120 per month, Mar 2025-Feb 2026. I have done it on Excel sheets manually, but I think there should be a way to do it with…
