I've attached a worksheet that has subtotals on it. I need to, on each subtotal line, put in the following formula (in cell E4):

=SUMPRODUCT($D2:$D3,$E2:$E

3/$D4)

THere are a LOT of subtotals on this sheet. I've been doing a copy paste from one subtotal line to another but everytime I paste I need to make sure that SUMPRODUCT is picking up the right ranges because they need to adjust based on the number of rows above. So, for example, in cell E12, the formula needs to be =SUMPRODUCT($D8:$D11,$E8:$E11/$D12) because there more lines of data involved in the subtotal. I need to automate this because it will take me hours to do this via copy/paste. I was copying, again for example, E7 to E12 and adjusting the numbers in the formula and then copying that formula to columns F, G, and H. I'm thinking that I can look for the word "Total" in column A (I'll have to parse out the word using a string function) but then I'm at a loss on how to continue. I would like to do this in VBA but if there's a quick way to do it otherwise I'd like to know that also.

Thanks

testsumproduct.xlsx
On I2 press ctrl-c

Select your data range (E2:H28)

Press F5

Click on special

Select Blanks and click on OK

now paste-special formulas.