Hi Winnie

I tried to follow your logic.

With your formula, you will get a wrong result if open and forecast
numbers are equal.

Try if this formula yield the correct result:

=C26+MAX(C10,C41-C10)

Best regards,
Per

On 1 Maj, 15:14, winnie123
wrote:
Hi there,

I am trying to project FY sales based on open orders,shipped orders and
forecast.

I have pulled together the 3 lots of data and used SumProduct to calculate
totals for each product by FY months (for this i have used Vlookup against
original data).

I have ended up with a sheet that looks like below

Open Orders
Columns C-W Row 3 have the Product Names
Column B Row 4 down to 15 have Oct,Nov ending with Sep (this is text)
C4 to W15 have the totals
Row 16 is the totals of each product

Shipped Orders
Columns C-W Row 19 have the Product Names
Column B Row 20 down to 31 have Oct,Nov ending with Sep (this is text)
C20 to W31 have the totals
C32 has the totals for each product

Forecast
Columns C-W Row 34 have the Product Names
Column B Row 35 down to 46 have Oct,Nov ending with Sep (this is text)
C35 to W46 have the totals
C46 has the totals for each product

I then want to pull all the sets of totals to create a projected sales for
this FY

using the same format as above, at first I thought to just add each of the
totals up but in some cases I have Open orders and forecast for the same
month, that I dont want to be added together.So I thought that I would assume
if Opens orders is greater than Forecast then add Open Orders to shipped
orders, if the open order is less than Forecast then subtract the open orders
from the forecast and add the shipped qty.

Using Apr which is on rows 10,26 and 41 the formula I have for the 1st
product is

=IF(C10C41,C10+C26,C41-C10+C26)

Is this the correct way or is there an alternative way, I am not sure my
logic is correct.

Thanks
Winnie