Summing across date bands

[smapple]

Hello,

I'm sure this is fairly straightforward, but I ended up doing a manual workaround that I'm keen to avoid in the future.

I have three columns of data. I want to know what the exposure was over each day, where the 'To' and 'From' dates are inclusive. For example, on the 3 January, the exposure would be 150 + 200 + 100 = 450. As you can see, the dates often overlap, which made the manual process pretty painful.

Is there a way, using VBA or a formula, to automate this? I tried concatenating the To/From fields and using a SumIfs but I got lost with the date convention. The convention I'm using is dd/mm/yy in my spreadsheet.

Many thanks for your help.

Exposure	To	From
150	1 Jan 2018	5 Jan 2018
200	3 Jan 2018	10 Jan 2018
220	12 Jan 2018	31 Jan 2018
80	15 Jan 2018	31 Jan 2018
100	1 Jan 2018	31 Jan 2018

<tbody>
</tbody>

 

[Tetra201]

With the target date (3 Jan 2018) in cell E2, try this:

=SUMPRODUCT(--(B2:B6<=E2),--(C2:C6>=E2),A2:A6)

 

[smapple]

Thank you, that worked perfectly!