Dashboard

Andrea_Beeharry2022

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.

kioshi43

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.

Andrea_Beeharry2022

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?

Genevieve P.

Hi @Andrea_Beeharry2022

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

Andrea_Beeharry2022

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")

Genevieve P.

Hi @Andrea_Beeharry2022

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:

https://youtu.be/Pb6IchMmSzU?t=1145

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