Can i use sumifs ?

[debster007]

The table below has several items that have multiple contracts and rates. I need a formula that prorates when the contracts change. For example, in the cashflow table for Item A, July 2016 should reflect 19 days @ 100 and 12 days @ 125 in July 2016.

Item	Start Date	End Date	Rate
A	5/19/2014	7/19/2016	$ 100
A	7/19/2016	7/19/2017	$ 125
B	11/28/2013	4/28/2016	$ 75
B	4/28/2016	4/28/2017	$ 80
C	5/1/2014	5/1/2015	$ 105
C	5/1/2015	4/30/2016	$ 110
D	7/1/2014	9/1/2016	$ 130
D	9/1/2016	9/1/2017	$ 135
Item:	A
Dates:	04/01/16	05/01/16	06/01/16	07/01/16	08/01/16	08/01/16		07/01/17
	04/30/16	05/31/16	06/30/16	07/31/16	08/31/16	08/31/16	. . . . . . . ..	07/31/17
Rate:	$ 100	$ 100	$ 100	<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"> See formula below</o:lock></vath></v:stroke></v:shapetype>	$ 125	$ 125	. . . . . . . . .	$ -
			current formula:	=SUMIFS($D$4:$D$11,$A$4:$A$11,$B$13,$B$4:$B$11,"<="&E14,$C$4:$C$11,">="&E15)

<tbody>
</tbody>

 

[C Moore]

Where do 19 days and 12 days come from?


edit: nevemind I see it

 

[C Moore]

It's more complicated than that. Try this, see if it will work. Paste it in the cell that you show see formula in your example.
=(LARGE(($C$4:$C$11-E14)*($C$4:$C$11-E14<=31),1)+1)*INDEX($D$4:$D$11,MATCH(E14,IF($A$4:$A$11=$B$13,$B$4:$B$11),1))+LARGE((-$B$4:$B$11+E15)*(-$B$4:$B$11+E15<=31),1)*INDEX($D$4:$D$11,MATCH(E15,IF($A$4:$A$11=$B$13,$C$4:$C$11),1)+1)
Use Ctrl+Shift+Enter

 

[debster007]

Brilliant! Thank you - that works.