Sales vs Target Spreadsheet RUNNING SUBTOTAL fix

Hi People,

Does anyone know a good formula fix to solve the problem in the spreadsheet attached. Basically the spreadsheet displays sales and targets for 6 financial periods. The targets for the full 6 periods are pre-set and loaded into the file, and the sales figures are loaded into the file as they become available. The issue us that the ALL PERIODS columns displaying the SUM of the sales and target is distorted because the full periods targets are present, but not the sales as yet, and consequently the % column is incorrect. Obviously I could manually alter the ALL PERIODS sales / target formulas to only include the completed periods, but I would like to know a formula that gets around this, as in reality I would need to make hundreds of changes each time.

I was thinking of using the COUNT function to count the number of nonblank Sales cells, and then somehow tell the ALL PERIODS target column to only SUM the corresponding number of target cells, but this is where I got stuck.

**UPDATE - I just figured out that I can use the COUNT function as outlined above, and then use the CHOOSE function to select the argument which SUMS up the relevant number of target cells. This works fine, but is a rather convoluted formula, and not overly quick to set dozens of times, so if anyone has a better method I would love to hear it! **

Any help much appreciated as always

James

One approach:

O3: =SUMPRODUCT(--(MOD(COLUMN($C3:$M3),2)=1),--ISNUMBER($B3:$L3),$C3:$M3)
copied down