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

  • Samuel Mueller
    Samuel Mueller 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?

  • Laura G
    Laura G ✭✭✭✭

    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

  • Samuel Mueller
    Samuel Mueller 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.

  • Samuel Mueller
    Samuel Mueller 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!