I have a doozy, i need formulas for each step & variable to summarize then create a dashboard. help!

Step 1---How Many (Limited Duration are full-time)

(Limited Duration are part-time)

(Contractors are full-time)

(Contractors are part-time)

etc.....


step 2-----Then If you are a contractor, ( How many Full-time /contractors work for Deloitte)...etc...

( How many Part-time /contractors work for Deloitte)...etc...


step 3------How many ( How many Full-time /contractors work for Deloitte/ are in the same meetings on specific days-Mon-Tues etc....)

step 4 --- How many Full-time /contractors work for Deloitte/ are in the same meetings the week )etc.....


Answers

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    Hi Bobby1,

    You should create a new Metric sheet that calculate metrics in above steps from your source sheet. Then from this Metric sheet you can show these metrics in your dashboard.

    COUNTIFS will be used for calculation. Look at the below formulas for your reference.

    Hope that help.


    Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

  • Bobby1
    Bobby1 ✭✭✭✭

    @Gia Thinh thank you

    in the summary sheet it returned "UNPARSEABLE


  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    The sheet summary is in your source sheet?

    can you post screen shots of the source sheet?


    Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 03/13/22

    Hi @Bobby1

    I hope you're well and safe!

    Try something like this. (I suspect another region + there was a semi:colon, comma missing)

    I've added both regional options for one of the formulas below.

    =
    COUNTIF([Staff Status]:[Staff Status], "Limited Duration",
    [Schedule Status]: [Schedule Status],"Full-time")
    


    =
    COUNTIF([Staff Status]:[Staff Status]; "Limited Duration";
    [Schedule Status]: [Schedule Status];"Full-time")
    

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey Bobby1

    Your screenshot is showing that you have used a COUNTIF (singular) function.

    A COUNTIF (singular) only allows one criteria in the formula. A COUNTIFS (plural) works in any situation as it allows any number of criteria - from one to whatever. This is why that function was recommended by Gia above. Change your function to COUNTIFS. As Andrée noted, you may have the regional comma-semicolon differences.

    Kelly

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 03/13/22

    @Kelly Moore

    Ouch! I missed the Countifs part. Time for coffee!

    @Bobby1

    Here are the modified formulas with COUNTIFS instead.

    EDIT: Added more options.

    =
    COUNTIFS([Staff Status]:[Staff Status], "Limited Duration",
    [Schedule Status]: [Schedule Status],"Full-time")
    


    =
    COUNTIFS([Staff Status]:[Staff Status]; "Limited Duration";
    [Schedule Status]: [Schedule Status];"Full-time")
    


    =
    COUNTIFS([Staff Status]:[Staff Status], "Limited Duration",
    [Schedule Status]: [Schedule Status],"Part-time")
    


    =
    COUNTIFS([Staff Status]:[Staff Status]; "Limited Duration";
    [Schedule Status]: [Schedule Status];"Part-time")
    


    =
    COUNTIFS([Staff Status]:[Staff Status], "Contractor",
    [Schedule Status]: [Schedule Status],"Full-time")
    


    =
    COUNTIFS([Staff Status]:[Staff Status]; "Contractor";
    [Schedule Status]: [Schedule Status];"Full-time")
    


    =
    COUNTIFS([Staff Status]:[Staff Status], "Contractor",
    [Schedule Status]: [Schedule Status],"Part-time")
    


    =
    COUNTIFS([Staff Status]:[Staff Status]; "Contractor";
    [Schedule Status]: [Schedule Status];"Part-time")
    


    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!