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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Exposure[/TD]
[TD]To[/TD]
[TD]From[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]1 Jan 2018[/TD]
[TD]5 Jan 2018[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]3 Jan 2018[/TD]
[TD]10 Jan 2018[/TD]
[/TR]
[TR]
[TD]220[/TD]
[TD]12 Jan 2018[/TD]
[TD]31 Jan 2018[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]15 Jan 2018[/TD]
[TD]31 Jan 2018[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]1 Jan 2018[/TD]
[TD]31 Jan 2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!