How to update complex formula when inserting rows.

[Methuselah1988]

So I have a spreadsheet with date running down columns. At the end of the row I have a formula in 7 columns. In each column it has the formula = IF(A2=AA1,B2,0) + IF(D2=AA1,E2,0) + and so on. The function is that at the end of each row there is a sum total for each variable represented in the 7 columns at the end.

My problem : easy enough to replicate the formulas to cover all rows, to add the sum variables of a,b,c,d,e,f,g in each row.

Each data set has 3 variables over 3 columns in each row: i.e. Jennifer in the middle column and underneath 3 variables. I'm am adding the sum total of variables such that if Jennifer makes a blue jumped on July 5th, the total of blue jumpers of Jenny, Jeffry and John would be visible in the end colum, as would sum total of red jumpers etc.

How do i ensure these formulas update and extend so when I insert these 3 columns for every data set, the formula = IF(A2=AA1,B2,0) + IF(D2=AA1,E2,0) adds a new data set, so changes to = IF(A2=AA1,B2,0) + IF(D2=AA1,E2,0) + IF(H2=AA1,I2,0) and so on and so forth. In an ideal world I would like to able to copy a data set and insert copied columns and paste he formula update and extend ad finitum.

For now I am adding these and extending the formula manually, and hiding the cells, but it is very laborious. Any help please? Want to not spend weeks adding empty data sets and extending formula for each of the 7 end columns by hand.

Basically I only want to add values if they are next to a variable which matches the end column.

 

[oldbrewer]

to enable us to understand better put a few rows of pretend data together and below it put what you want to end up with

you are familiar with your data, your words are very clear to you, but not to me, sorry.