I am looking to devise new metrics in excel using certain combinations.

The first thing is a table that I have created. This table assigns values to 30 items in a list that have been numbered from 1 to 30. This list is generated based on certain prevailing factors and will be updated on a weekly basis.

Each of the 30 items in the list is color coded based on an existing system of classification. Based on the colors that correspond to each position, I would like to set up a formula or a macro to automatically tally all the values. Based on the values collected pertaining to each color, I would like to create a line graph. Now remember I said I’ll be adding the table with 30 values every week? This graph will capture all the changes recorded over a period of time.

In addition to this, I will also have another metric that similarly takes into consideration colors corresponding to each position and their sum to create a bar graph. This too shall be updated every week.

Any tips on how I can go about creating something like this?

Thanks a ton folks!