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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much @Genevieve P. cheers
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!