Sumproduct: Financial year data |General Excel Questions & Answers|Excel Forum|My Online Training Hub

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