Need help please

 Hi wondering if anyone can help me I have set up a smartsheet that my staff fill in via the form each day (example 1) for the amount of jobs they have completed. I created a summary sheet (example 3) which gives a count of all completed which feeds into a Dashboard, but I need to get the number for each staff member of what they have completed (example 2), I have tried a few ways but don't seem to be getting anywhere?

Example 1


Example 2


Example 3


Answers

  • Kelly Drake
    Kelly Drake Overachievers Alumni

    If you have Pivot App you can get both of your examples pretty easily. If you don't have that add on there are a couple of ways to get the info....


    Example 3 would likely be completed the simplest way via Sheet Summary cells for each category with a SUM Formula.... ex: SUM([Coupling / Uncoupling]:[Coupling / Uncoupling]). Once you have all of your sheet summary fields created with the formulas, you can easily use that to create dashboard widgets.


    To get to the metrics in example 2 - you would you SUMIF formulas. At the bottom of example 2 it looks like you have a people hierarchy in addition to the type of work. It may be easier to have the people hierarchy but then separate out the type of work by column. Otherwise, you'll need to build formulas for each individual cell rather than building one formula for each column.

    If you shift to the types in each column and the people heirarchy you could use this formula

    =SUMIF({reference Driver Trainer}, $[Driver/Trainer]@row, {reference column for 1st type})

    The $ sign makes it so that as you copy your formula across the reference stays the same. You would need to create the second cell reference in the formula above for each column /type of work but you would only need to create the reference for the name of hte person once.

    Hope this helps!

    https://help.smartsheet.com/function/sumif

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • Thanks Kelly got it all working now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!