How to count names and return a total for the number of times the name appears in a column

Options

Hello Community, I am trying to create a formula that will count the total amount of tasks assigned to both Robert Stubbs and Michael Ramos based on the number of times their names appear in both the 'assignee' and 'QAT/Oversight' columns. I would like to display the total for each person in the 'Details' column next to 'Work Allocation to Robert Stubbs' (a similar row will also be created to report the total number for Michael Ramos).

Is this possible in Smartsheet, and if so, any guidance on the formula to use to in order to generate a count would be very appreciated.


Best Answer

  • Abraham Sanchez
    Abraham Sanchez ✭✭✭
    Answer ✓
    Options

    Hi Tamiko,

    I think the following could work:

    1) Created two text number data type helper column one for Assigneed (Name: AssigneedText) and another for QAT/Oversight (Named: QAT/OversightText), This so we can use the formula on text instead of contact type, so they work.

    2) Create column formula so AssigneedText = Assigneed@row and QAT/OversightText = QAT/Oversight@row. You may hide those columns.

    3) then in Work Allocation to Robert Stubs datails use the following formula

    =countif([AssigneedText ]:[AssigneedText ], Contains("Robert Stubs",@cell)) + countif([QAT/OversightText ]:[QAT/OversightText ], Contains("Robert Stubs",@cell))

Answers

  • Abraham Sanchez
    Abraham Sanchez ✭✭✭
    Answer ✓
    Options

    Hi Tamiko,

    I think the following could work:

    1) Created two text number data type helper column one for Assigneed (Name: AssigneedText) and another for QAT/Oversight (Named: QAT/OversightText), This so we can use the formula on text instead of contact type, so they work.

    2) Create column formula so AssigneedText = Assigneed@row and QAT/OversightText = QAT/Oversight@row. You may hide those columns.

    3) then in Work Allocation to Robert Stubs datails use the following formula

    =countif([AssigneedText ]:[AssigneedText ], Contains("Robert Stubs",@cell)) + countif([QAT/OversightText ]:[QAT/OversightText ], Contains("Robert Stubs",@cell))

  • Tamiko Smith
    Options

    Hi Abraham. Many thanks for this formula. It worked like a charm and has saved me a world of headache. I appreciate your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!