Closing stock formula needed

[Balmer07]

Hi,

I need some help to automatically calculate my closing stock using a formula (using December 21 as an example)

In column 'D' I have date purchased
In column 'E' I have date sold
If not yet sold then column 'E' is blank
Value of stock purchased is in column 'G'

I need a formula to calculate the closing stock figure for December which must also include any unsold stock from previous months. The formula also needs to consider that when the stock is sold the closing stock figure for December cannot change.

The closing stock cell will need to work from the month and year which will be in another cell and written as 'Dec-21' or 01/12/2021 when you click into the cell.

Hopefully this makes sense and I would appreciate the help.

Steven

 

[Balmer07]

Manage to resolve using the below formula which I worked out:

=SUMIFS(Master!$G:$G,Master!$D:$D,"<="&EOMONTH(B3,0),Master!E:E,"")+SUMIFS(Master!$G:$G,Master!$D:$D,"<="&EOMONTH(B3,0),Master!$E:$E,">="&EOMONTH(B3,0))