# Can i use sumifs ?

#### debster007

##### New Member
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 See formula below \$ 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>

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Where do 19 days and 12 days come from?

edit: nevemind I see it

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

Brilliant! Thank you - that works.

Replies
7
Views
610
Replies
6
Views
756
Replies
7
Views
742
Replies
0
Views
435
Replies
3
Views
764

1,196,273
Messages
6,014,382
Members
441,817
Latest member
biRosETa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back