Index Collect formulae
Hi,
I am using the below formulae to bring in the $ value tied to a Project ID. Works fine. - =INDEX(COLLECT({YTD Revenue}, {P ID}, CONTAINS([Reference / Notes/ P. ID]@row, @cell)), 1)
Issue - Whenever there are scenarios with 2 or more Project IDs, I have to replace Reference / Notes/ P. ID with the actual "project ID" and then use the same formulae twice supported by a "+" sign in between. Or I do a Sum(Index(Collect())
Is there a way, that I can use the above listed formulae string but write the 2 Projects ids separated by comma or in parenthesis or something in such a way that Reference / Notes/ P. ID]@row find the distinct $ values and adds them.
Answers
-
You could use a JOIN(COLLECT instead, which would join together your multiple values into one cell so you can see them as distinct values, however this would not SUM the values. See: Gather all matching content into one cell
For example, if you had the same Project ID with two rows, one that says 10 and one that says 5, the formula would output:
10, 5
instead of
15
Try:
=JOIN(COLLECT({YTD Revenue}, {P ID}, CONTAINS([Reference / Notes/ P. ID]@row, @cell)), ", ")
Is this what you were looking to do? The alternative would be to use SUMIFS which would Sum all your values together, and return 15. See: SUMIFS Function
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
HI @Genevieve P. I am using this formula to get the unique values, however it is saying incorrect argument, could you please help
-
Hi @Vikas85
You just need to swap around your DISTINCT and COLLECT functions!
=INDEX(DISTINCT(COLLECT([Column 30]:[Column 30], [Row Number]:[Row Number], >=1)), 1)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you so much @Genevieve P. cheers
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!