Formula Help Please - Sheet Summary

Hi everyone - I have a GIANT sheet I'm working with that contains our project portfolio. I need to add summary fields to capture Project Status (from a dop down field in a column) across 10 different Sponsor Divisions (also in a drop down field within a column).

I can't seem to crack this one. Can someone help? I'm sure it's a CountIF function...but...

thanks in advance!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @ErinM

    Don't give up! We can help!

    Can you clarify if your dropdowns are multi-select or single select?

    Instead of adding two COUNTIFS together, you would write it all in one COUNTIFS. IT sounds like your Status column is a single select, so there's no need to use CONTAINS.


    Try this:

    =COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), Status:Status, "Submitted")


    Then, yes! You can just swap bout "Submitted" to be your other values for the other counts.

    Let us know if this worked for you! If not, I agree that screen captures would be helpful so we can check out your column set up, but please block out sensitive data.

    Cheers,

    Genevieve

Answers

  • GDHernandez
    GDHernandez ✭✭✭✭

    Hello,

    I am by no means an expert, but I'm wondering if it would be something like this:

    =COUNTIF([Column Name]:[Column Name], "Complete")

    "Complete" would be whatever your status' are labeled.

  • ErinM
    ErinM ✭✭

    For what it's worth, this isn't working...

    =COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell)) + COUNTIFS(Status:Status, CONTAINS("Approved", @cell))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @ErinM

    I hope you're well and safe!

    That should work.

    Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    I hope that helps!

    Be safe, and have a fantastic week!

    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, Awesome, 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:[email protected] | P: +46 (0) - 72 - 510 99 35

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

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭

    Hi @ErinM

    I apologize in advance for the long answer here:

    If I follow/anticipate correctly, you would want each Sheet Summary Name to be each of the Sponsor Divisions and with the field be the options for status with the counts. If that's the case, below is a formula that would work with some tweaking obviously to use your data.

    ="Not Started - " + COUNTIFS([Sponsor Division]:[Sponsor Division], "EAST", [Project Status]:[Project Status], "Not Started") + CHAR(10) + "In Progress - " + COUNTIFS([Sponsor Division]:[Sponsor Division], "EAST", [Project Status]:[Project Status], "In Progress") + CHAR(10) + "Complete - " + COUNTIFS([Sponsor Division]:[Sponsor Division], "EAST", [Project Status]:[Project Status], "Complete") + CHAR(10)


    Separately from Sheet Summary, it might be better to visualize it on a Dashboard. To do so you would create a new Sheet, maybe called Metric Sheet. Depending on how you want to see the data, what I'm describing below might need to change a little but...

    You could list all the Sponsor Divisions in Column1, skipping the first row. Then add all the status types across the columns in Row 1. Next you'll add a COUNTIFS formula counting the other sheet against column 1 and row 1.

    This would be good becuase you could drag the formula down and across and not have to edit each lookup value. Once that table is built you could display it with graphs and/or charts, or straight metric counts.

    I hope that helps.

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet PLATINUM Partner

  • ErinM
    ErinM ✭✭

    Thanks for the replies. What's tricky is that I'm actually trying to build out summaries in the sheet to link to the Metrics Widget in a dashboard.


    @Matt - I tried to do EXACTLY what you're saying with a Metrics sheet so that I could point all of my widgets to it. I failed. I'm not well versed at this yet...in particular creating a new sheet with data from others.

  • ErinM
    ErinM ✭✭
    edited 02/24/23

    also, @Matt Johnson - I think what you are summarizing is right. Your formula is the inverse of what I wrote? This formula gives me an incorrect value rather than an error:

    =COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell)) + COUNTIFS(Status:Status, CONTAINS("submitted", @cell))

    In my sheet, there are eleven values that start with "OneIT". There are 7 different statuses. I was hopeful to create 7 different summary fields that use the same formula but change out the "status name".

    Maybe my @cell is throwing the error?

  • ErinM
    ErinM ✭✭

    Last attempt: =COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT")) + Status:Status, ="Submitted")

    Not working. :(

  • ErinM
    ErinM ✭✭

    Okay, for real, this is my last attempt:

    =COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell)), IF([Status]:[Status]= ("Submitted, @cell))

    I've tried it with COUNTIF and with COUNTIFS. I've tried it with and without @cell in it. I give up!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @ErinM

    Don't give up! We can help!

    Can you clarify if your dropdowns are multi-select or single select?

    Instead of adding two COUNTIFS together, you would write it all in one COUNTIFS. IT sounds like your Status column is a single select, so there's no need to use CONTAINS.


    Try this:

    =COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), Status:Status, "Submitted")


    Then, yes! You can just swap bout "Submitted" to be your other values for the other counts.

    Let us know if this worked for you! If not, I agree that screen captures would be helpful so we can check out your column set up, but please block out sensitive data.

    Cheers,

    Genevieve

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @ErinM

    Can you share screenshots? That would help to figure out what's going on.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

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

  • ErinM
    ErinM ✭✭

    That worked, @Genevieve P. - thank you so much!!! Both of the columns are drop downs with single select options only.

    Oddly, when I tried to point at another column for a different value in a new summary field, I'm getting an error.

    =COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), Project Category:Project Category, "University/Division Initiative"). For some reason it's not picking up the Project Category column?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @ErinM

    I'm glad to hear it worked!

    Any time a column name has a space or number you'll need to surround the name in [square brackets] so the formula knows when the column name starts and stops, like so:

    =COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), [Project Category]:[Project Category], "University/Division Initiative")

    Cheers,

    Genevieve

  • ErinM
    ErinM ✭✭

    Aha, darn brackets! This worked:

    =COUNTIFS([Sponsor Division]:[Sponsor Division], CONTAINS("OneIT", @cell), [Project Category]:[Project Category], "University/Division Initiative")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!