
New Member

December 9, 2016

Good morning,
I am trying to allocate monthly volumes & Costs into a Financial Year (1 April to 31 Mar) for the last 3 years, bearing in mind the contract dates, but I seem to get duplicates when the contract runs out within the year & is re-signed at a different price. Both volume and cost are duplicating for the last month of the contract. I think my formula is way too long and convoluted but do not know how to sort it out!
Formula is as follows:
=IFERROR(IF(($K7>=AR$1)*AND($L7>AR$4),SUMPRODUCT(('Volume sold to customer'!$C$4:$AU$4>=EOMONTH('Margin Overview'!$K7,-1)+1)*(('Volume sold to customer'!$C$4:$AU$4<=EOMONTH(AR$4,0))*('Volume sold to customer'!$A$6:$A$3000=$C7))*'Volume sold to customer'!$C$6:$AU$3000),IF(($L7<AR$4),SUMPRODUCT(('Volume sold to customer'!$C$4:$AU$4>=EOMONTH('Margin Overview'!AR$1,-1)+1)*(('Volume sold to customer'!$C$4:$AU$4<=EOMONTH($L7,0))*('Volume sold to customer'!$A$6:$A$3000=$C7))*'Volume sold to customer'!$C$6:$AU$3000),SUMPRODUCT(('Volume sold to customer'!$C$4:$AU$4>=EOMONTH('Margin Overview'!AR$1,-1)+1)*(('Volume sold to customer'!$C$4:$AU$4<=EOMONTH(AR$4,0))*('Volume sold to customer'!$A$6:$A$3000=$C7))*'Volume sold to customer'!$C$6:$AU$3000))),0)
where K7 is contract start date & L7 is contract end date.
'Volume sold to customer'!$C$4:$AU$4 is the month (from Aug 2015 to April 19)
AR$1 is 1st April of relevant year (1st day of FY)
AR$4 is 31 Mar of relevant year (last date of FY)
Can you help please as I seem to be banging my head off a brick wall?
Am I making it too complicated?
Many thanks,
Richard
1 Guest(s)
