How to count names and return a total for the number of times the name appears in a column
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
-
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
-
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))
-
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
Categories
Check out the Formula Handbook template!