# Index/Collect into different columns depending on contract type

I have a multi select column with a list of employees. Each of these have a different contract type which is listed in a seperate sheet. I want to keep the master list of employees on my current sheet but then break it down into columns of whose contract is permanant, part-time or contract worker.

This gives me a list of contract types but I can't seem to deduce myself how to change it to return the employee names into the correct columns for contract type.

=INDEX(COLLECT({Name}, {Contract}, [EMPLOYEE NAME]@row), 1)

Any thoughts?

I've used this formula but it returns all contract types for all employees on that row.

=JOIN(COLLECT({Contract}, {Name}, CONTAINS(@cell, [EMPLOYEE NAME]@row)), " , ")

I think I've sorted it by including the contract type to search for and then return the names of those who have that contracr type.

=JOIN(COLLECT({Name}, {Contract}, "3rd Party", {Name}, CONTAINS(@cell, [EMPLOYEE NAME]@row)), " , ")

I must have been having a Friday afternoon brain fart so thanks Paul! 😎

• ✭✭✭✭✭✭

On a row by row basis, you would need separate columns for each selection type. So a column for the 3rd Party count, a column for the LMSA count, so on and so forth. From there you would use this to get your counts:

=(LEN([Contract Type]@row + ",") - LEN(SUBSTITUTE([Contract Type]@row + ",", "abc", ""))) / LEN("abc")

Each instance of "abc" in the above formula should be replaced with whatever you are wanting to get the count for.

• ✭✭✭✭✭✭

Try switching your two cross sheet references around so that {Contract} is first.

I've used this formula but it returns all contract types for all employees on that row.

=JOIN(COLLECT({Contract}, {Name}, CONTAINS(@cell, [EMPLOYEE NAME]@row)), " , ")

I think I've sorted it by including the contract type to search for and then return the names of those who have that contracr type.

=JOIN(COLLECT({Name}, {Contract}, "3rd Party", {Name}, CONTAINS(@cell, [EMPLOYEE NAME]@row)), " , ")

I must have been having a Friday afternoon brain fart so thanks Paul! 😎

• Hey @Paul Newcome one final question! How would I do a count of Contract results? So if I had a cell which returned every contact type and I wanted to do a count on 3rd Party, SSA, etc. I can't get any of the below to work properly and it's the last piece of my puzzle 🤧

=COUNTIF([CONTRACT TYPE]@row, CONTAINS(@cell, "3rd Party"))

=COUNTIFS({Contract}, CONTAINS(@cell, "LMSA"), {Name}, [EMPLOYEE NAME]@row)

=COUNTIF([CONTRACT TYPE]:[CONTRACT TYPE], FIND("LMSA", @cell) > 0)

• ✭✭✭✭✭✭

What would be the expected output for "3rd Party"?

• It should be a number but I've looked at my roadmap again and I can remove this step if I just do a count of names returned instead 🤦‍♀️

Hopefully my explanation of what I'm trying to achieve below is helpful!

We currently have a dropdown, multi-select column for employees assigned to a particular job. I then have a column which does a count of the number of employees selected: =COUNTM([EMPLOYEE NAME]@row)

I have another column which tells me which employees are on a full time contract (LMSA): =JOIN(COLLECT({Name}, {Contract}, "LMSA", {Name}, CONTAINS(@cell, [EMPLOYEE NAME]@row)), ", ")

I then need a count of the names in this column. I was hoping this formula would work again but it only brings back an answer of one each time: =COUNTM([EMPLOYEE NAME]@row)

End goal is that we know we have six employees assigned to X job and of these, four are on a full time (LMSA) contract and therefore no action is required. However two are 3rd Party so a contract will need to be drawn up and signed for Mike and Steve before we can start the job.

• ✭✭✭✭✭✭

I understand that it should be a number, but what would be the number expected ONLY based on your most recent screenshot? Would it be 2 because there are 2 cells with "3rd Party" in them, or would it be 9 because there are a total of 9 occurrences of "3rd Party"?

• Oh sorry! It would be per row so 5 and 4

• ✭✭✭✭✭✭

On a row by row basis, you would need separate columns for each selection type. So a column for the 3rd Party count, a column for the LMSA count, so on and so forth. From there you would use this to get your counts:

=(LEN([Contract Type]@row + ",") - LEN(SUBSTITUTE([Contract Type]@row + ",", "abc", ""))) / LEN("abc")

Each instance of "abc" in the above formula should be replaced with whatever you are wanting to get the count for.

• Oh you genius!!! Thanks so much for your help Paul!

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!