Need help with creating a formula to Sum columns and drag and drop

[OhYouDidntKnow]

Hello,

I am having a little difficulty on a project I am working on.

In the sheet attached below I have a set of names in column A. Those same names are spread horizontally through G1:R1 using the transpose function. Starting in cell B2, I am looking for a formula to be able to sum each column that matches their name above. So in cell B2, I want to sum column G2:G16 and then divide by 15. I entered the formula "=sum(G2:G16)/15", however, from here I am unable to drag and drop where B3 will go to column H2:H16, and etc.

I hope this makes sense.

Greatly appreciate any help.

[ChemistB]

Try in B2 copied down
=SUM(INDEX($G$2:$R$16,0,ROWS($B$2:$B2)))