Formula Help

[David Heaton]

Hi all,

I need to calculate GM on products sold and include any giveaway items
in the caluculation.

I have a table similar to the one below where the 'bonus' products are
listed below their corresponding charged item.
I need a formula in the GM column that will check to see if there are
any items below the current item with 0 sell price, sum the cost of
those items and then use that sum to calculate the GM%


Sell Price Cost GM
Product A 50 25 26%
Bonus A 0 8 0%
Bonus B 0 4 0%
Product B 58 30 36%
Bonus A 0 7 0%

I'm currently using SUM(OFFSET(D4,0,0,MATCH(0,E4:$E$11,-1),1)) in part
of the formula however this is producing inconsistent results where
for some unknown reason, it wont calculate correctly.

Any ideas would be great.

Thanks

Can anyone think of a better, more reliable solution.