Index Collect formulae

Gaurav Chauhan

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.

Genevieve P.

Hi @Gaurav Chauhan

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

Vikas85

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

Screenshot_2.jpg

Genevieve P.

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

Vikas85

Thank you so much @Genevieve P. cheers