Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Counting "Comments" in a Conversation?

I would like to count the number of "comments" in a conversation because I want to use that to track the progress of an issue/project. The # of comments would equal the # of times that issue/topic was engaged to easily identify topics that may need escalation.

Is there any way to do that?

Answers

  • Overachievers

    @squirrelax I do not believe there is a direct way to achieve this. However if your organization has Bridge for Smartsheet or if you are familiar with the API you could do this.

    Alternatively, you could add an auto row number column (Lets say it's named "UniqueRow") to your sheet if you don't already have one, and also add the Latest Comment Column to your sheet, then you could use a copy row automation and cross sheet references to achieve a count.

    Then you would essentially have a helper sheet (lets say its called "Comment Count") that just receives copied rows from your source sheet, triggered when the latest comment column changes to any value. You could create this sheet by saving your current sheet as new, but clear the data.

    You would need a cross sheet reference to the Comment Count sheet for "UniqueRow".

    Your formula on your source sheet in your count column would be:

    =countifs({CommentCountAutoRowNumber}, [UniqueRow]@row)

    {CommentCountAutoRowNumber} would be whatever your cross sheet reference name is.

    Does this make sense?

  • ✭✭✭✭

    Hi @Samuel Mueller

    I am looking to set this up but wanted more clarification around the process. What do you mean by "You could create this sheet by saving your current sheet as new, but clear the data"

    I want to count total conversations our team has had in a date range say of 1 week on a sheet

    Thank you

  • Overachievers
    edited 01/10/24

    @Laura G That was just to get a new similar sheet but starting from scratch. Mostly because a copy row will add all the columns anyway. This second sheet is just what would be used to count the comments.

    To specifically answer this question this is what I meant: uncheck "Sheet Data and Formatting". Then you would get a copy of your sheet, but it would have no data in it, so you would start fresh with the helper sheet.

    **Sorry I misread who tagged me.

  • Overachievers

    If you want to count numbers in a week, you would have to add a date column on the helper sheet, then in that helper sheet create a record date automation every time a new row is added. Then you can use that in your criteria

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions