Conditional Cumulative Sum based on multiple columns

Question

I have a simple inventory table in excel that looks like this:

Number of Items    |    Date Incoming    |    Date Out
-------------------------------------------------------
             10    |    1 Jan 2018       |  30 Jan  2018
             30    |   15 Jan 2018       |   1 May  2018
             20    !    1 Feb  2018      |  15 Mar  2018

I would like something that can give me the the total number of items that are present in the inventory at each date, that is:

 1 Jan 2018  |    10
15 Jan 2018  |    40
30 Jan 2018  |    30
 1 Feb 2018  |    50
15 Mar 2018  |    30
 1 May 2018  |     0

What I was thing is some sort of cumulative sum where the number of items are added at "Date Incoming" and substracted at "Date Out".

Can you help me? I would prefer to avoid macros but even a vba solution if fine.

Answer 1

For a given date, you can do:

=sumif(@DateIn, "<="&@CellWithGivenDate, @NumberOfItems) - sumif(@DateOut, "<="&@CellWithGivenDate, @NumberOfItems)

With @NumberOfItems, @DateIn, and @DateOut being columns 1 to 3 of your sample, and @CellWithGivenDate being the relevant cell in column 1 of your expected result sample.