[TABLE="width: 500"]
<tbody>[TR]
[TD]Colour
[/TD]
[TD]Amount
[/TD]
[TD]Rank
[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]6[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]10[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
How would I calculate the rank column given the first two columns as a calculated column? Basically each row looks at the colours and then sums the Amount for that colour. Then shows the rank for its colour. Ranks are therefore repeated in multiple rows.
<tbody>[TR]
[TD]Colour
[/TD]
[TD]Amount
[/TD]
[TD]Rank
[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]6[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]10[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
How would I calculate the rank column given the first two columns as a calculated column? Basically each row looks at the colours and then sums the Amount for that colour. Then shows the rank for its colour. Ranks are therefore repeated in multiple rows.