I am trying to put together a list of our project managers and which business units each of them are responsible for. I need it to be in a certain format because right next to each of the business units, I am going to have 3 COUNTIFS formulas to count different things based on the project manager and business unit. I already have the COUNTIFS formulas working. I just need help indexing the business units based on the project manager. I am putting up to 10 business units per project manager. Most likely they will not have 10. The ultimate goal is to put this sheet in a report that will list the business units grouped by the project managers. Here is what I have so far with the formula and the results:

=INDEX(DISTINCT({Business Unit}), MATCH([Project Manager]@row, {Perk Partner}, [Row ID]@row), [Row ID]@row)

Any help with this will be greatly appreciated.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!