Sum distinct values grouped by a column

[leandrovegacr]

Hi everyone.

I have a collection that, for the simplicity of the example, have three columns: Value, Index, Category. Each index has the same value but different categories, and there may be any amount of indexes. What I need is to get the value representing each index, and then, sum all those values. It's important to note each index will always have the same value, this will never change, but different indexes could share a value. What is not possible is for a same index to have different values.

So, let's say my collection is:

Value	Index	Category
100	1	a
100	1	b
120	2	c
100	3	d
100	3	e

So, from this, I need to get 320: 100 representing the index 1, 120 representing the index 2, 100 again representing the index 3.

Ideally, This could be calculated without any button pressing, just appending the formula to a text box so it's a value always kept true as the collection is modified.

I have been trying with GroupBy and Distinct, but have not get what I need.

Thanks for any help!

[RandyHayes · Accepted solution]

@leandrovegacr 

Consider the following formula:

Sum(
    GroupBy(yourList,
        "Index", "Value", "_items"
    ),
    Value
)

I hope this is helpful for you.

[leandrovegacr]

It is, definitely, thanks!