# Index Collect formulae

Options
✭✭✭✭✭✭

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.

edited 07/18/22
Options

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

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

• ✭✭
Options

HI @Genevieve P. I am using this formula to get the unique values, however it is saying incorrect argument, could you please help

Options

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

• ✭✭
Options

Thank you so much @Genevieve P. cheers

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!