Percentage of total product mix for each country/geographic region

Hi Superusers,

I am running into an efficiency related problem when I am trying to calculate a percentage of total product mix for each geographic region. Please refer to the attached. In the example, I have the same number of rows/products under each geographic region, and for each product (i.e., product 1, product 2, product 3), I want to calculate the percentage of total in columns M to V (please kindly refer to the formula I have right now). It is pretty simple if I only have a few geographic region, I can drag the formula down for all regions. And for each region, I manually change the denominator to the sum of product 1, product 2 and product 3 as in my original formula, I have locked the rows to row 9, 10, 11. My question is, is there a more efficient way/or a formula so I can simply drag the formula down without manually updating the denominator every time assuming I have many regions (with over 5000 rows of data)? I did some research on the offset function but can't wrap my head around how it could help in this situation. Appreciate the help in advance!

Hi Glen,

The formula starts from cell M9, and I intend to drag the formula down the rows to make it efficient (across column M to column V and down the rows), but the problem is, for example, in row 22, the total is suppose to pick up row 22 to 24, but due to the nature of my original formula, it is picking up row 9 to row 11 (locked in rows). To make the analysis work, I will have to manually update the sums to pick up rows 22 to 24 (and I will have to repeat this manual process for each geographic region). Is there a way I can apply one formula without the manually changing the sums for each geographic region? Please let me know if this makes sense.

Great job Glenn. I've been messing with this too, the original data layout wasn't usable. . .I had looked for a way to autopopulate the country names every 12th row, but I couldn't get there. . .then to calculate the percentages. . ughh. Good work.

Pete