Distribution options?

Okay, you'll need to modify your sheet a bit to make the tricky formula less complex.
1. Insert enough columns so that your tables start in column I (i.e. 'Sales' should now be in cell I2). You can hide these empty columns if you wish.
2. In cell J23 (where your 300,000 is) enter this formula:
=SUMPRODUCT(N(OFFSET(A3:J3,,{9,8,7,6,5,4,3,2,1,0},1,1)),$J12:$S12)
3. Drag that formula across to S23 and then down to S27

I see that you may wish to add more columns. The formula above would need to be adjusted. For example the {9,8,7...0} array would need to expand. You could do that with something like 10-COLUMN($A:$J), but I thought that may add too much confusion as I wanted you to be able to understand how it works (use 'Evaluate Formula') so that you could expand it in the future. Alternatively, you could create a reversed Sales table and use a shifting array to use that to multiply against your percentages. I started going down that path, but it would be harder to explain and adding the blank columns seemed easier.

Thanks for the suggestion. It works well, but it not as flexible as I would like (yet).

Is there any way to set the offset part in the formula as a reference? In my localized version of excel it looks a bit different.
{9\8\7\6\5\4\3\2\1\0}

Say would it be possible to reference the first one as {0} in a different cell (J21), the second as {1\0} (in J22), etc etc.
I tried using the INDIRECT function but that didn't work.

I'm also curious how you would use a reversed Sales table and the shifting array. Would that be a more flexible option?

This uses the reversed sales table method (which then removes the need of the 'filler columns' in front); however, it does need to have the 9 cells to the right of the reversed sales table to be unused, and that 9 would increase as your table adds more columns.
1. I inserted a enough rows for the reverse table. Mine is located at B8:L13 and the 'Distribution' cell is now at B15.
2. In C8 type this formula (the result should be X+9):
=INDEX($C2:$L2,COLUMNS($C2:$L2)-COLUMN(A:A)+1)
3. Drag that formula to fill your reverse sales table
4. In C27 (it should have your 300,000 number in it) insert this formula:
=SUMPRODUCT(OFFSET($L9,,SUM(COLUMN($C:$C)-COLUMN(C:C)),1,COLUMNS($C$9:$L$9)),$C16:$L16)
5. Drag that formula to fill you Result table

Rather than hardcoding the {9,8,7...0} I used the COLUMNS and COLUMN formulas to create the same array, but those used ranges will need to be changed manually, or, if you are tricky, with dynamic named ranges.

C23=iferror(sumproduct(subtotal(9,offset($c12,0,row(indirect("1:"&min(columns($c23:c23),count($c12:$l12))))-1))*subtotal(9,offset(c3,0,-row(indirect("1:"&min(columns($c23:c23),count($c12:$l12))))+1))),0)

Please Login or Register  to view this content.

try the above formula and copy across

you are welcome and thanks for adding reputation