Sumproduct

[scooterboygnu]

Hi All,

I need to calculate the value of a deal during the current period. By this I mean that if a deal start date is Nov 2011 and lasts 12 months then I will require the value for Jan 2012 - Oct 2012.

I have a large date set to apply this to so it need to look up the "Org name" and bring back the total for that.

I have tried a few SUMPRODUCTS with OFFSET but can not quite get this to work.

I have attached a basic view of what I need. Your help is greatly appreciated.

Regards

[NBVC]

Try perhaps:

=SUM(OFFSET(INDEX($B$2:$M$13,MATCH(A19,$A$2:$A$13,0),MATCH(MAX(B19,$B$1),$B$1:$M$1,0)),0,0,1,D19+(B19=MAX(B19,$B$1))))

double check your expected result for E20, should it not be sum for Jan to June if you are looking at 6 months in 2012?

[scooterboygnu]

Many thanks NBVC.Can this be altered to ignore the cell with number or 2012 months and still perform the same calc?

[NBVC]

So how would you know the number of cells to sum up?