Dashboard
I have a sheet that has all the jobs we have by unit. each person is assigned a job in each unit. i want to be able to create a dashboard that shows how many jobs each person has in that unit. any help with formulas and how to get started would be appreciated.
Answers
-
Depending on how your sheet is set up, you could try using:
=COUNTIFS([range with names], "name", [range where jobs are], "job")
Other formulas might work better though depending on how the sheet looks.
Once you got your counts, then you can either use a chart or report on your Dashboard to display the number of jobs each person has in their unit.
-
Thanks so much for your response! this is what i have the sheet set up as.
should i create a formula sheet and reference the sheet i want to use. How do i go about doing that?
-
Yes exactly! You can create a new sheet to house your formulas and use as your reference for the chart.
In this instance, you'll want to use Cross Sheet ranges in the COUNTIFS Function:
=COUNTIFS({Column with names}, "name", {Column where jobs are}, "job")
You can replace the values "in these" with a value in the cell to the left of your Metric sheet:
=COUNTIFS({Column with names}, [Name]@row, {Column where jobs are}, [Job]@row)
See: Advanced Cross-sheet Formulas
This webinar has an example of COUNTIFS: Formulas webinar series
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
this helps but i think i need to know exactly what to put in the formula because no matter what i try, i seem to not be able to use the formula. this is what i am entering.
COUNTIFS({engineer}, "a name from the column", {unit}, "job")
-
Each of the references {in these} are cross sheet references that you will need to select individually from your source sheet when building out the formula. This means you can't copy/paste examples from the community as the link to that source sheet hasn't been created yet.
This structure should work:
=COUNTIFS({engineer}, "a name from the column", {unit}, "job")
But make sure that {engineer} and {unit} were both created by clicking this link in the formula window:
I would recommend watching the first Formula Webinar I linked above to see how a COUNTIFS formula is created from start-to-finish. It's at the 19:00 minute mark, here:
Let me know if this helps! If not, it would be useful to see screen captures of your formula actually in Smartsheet, but please block out sensitive data.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 359 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!