Formula Needed - Inventory Tracking

I hope somebody can help! I am looking for a formula that will pull data based on the # of columns that separates itself from certain text. Ex: Column 1 = T-Shirt Color (let's say Red). Column 2 = Small, Column 3 = Medium, etc... I have a table with all current inventory based on each color and size, but my peers do not want to continually add their sizes to the sheet (basically they do not want to do the math). Is there a formula that allows them to paste their size totals (Ex - Red/1/4/5/4) and have the table above add those numbers to the current totals?

A B C D E F
Red 1 4 5 4 0

The "totals" table would include the formula in each cell and add it to the current totals (something similar to if Column A = "Red" then add the data in column B to the selected cell)

I appreciate any help! This would be easier than convincing the group to pull out a calculator!!

Welcome to the Board.

Given you want your inventory table to hold a rolling count/aggregate, i.e. current balance + latest entry (as appl.), you would either need to:

a) enable iterative calculation - to permit circular calculations, or
b) use VBA

most would err towards b)

If you want to go down the VBA route I would suggest either

a) closing this thread (see FAQ) and posting a new question in the VBA forum, or
b) reaching out to a Moderator to have them move this existing thread on your behalf.