I cannot download your example due to security settings here, but it sounds like you need to create a couple of counters.
sum([fee from sale])
These should give you an idea of what you need. Place each of these on your sheet without the need of the filter that brings everything down to just available and you will have a summary count of stock.
Thank you, this does what I need within this example.
Is there an easy way to incorporate additional activity types?
This calculation achieves the result if a sale is entered, but what about if that sale falls off? Also, if the property withdraws from the market without selling it would also need to be removed from available stock.
My full database uses the following four activity types: Listing (adds to stock), Sale (removes from stock), Sale off (adds to stock) & Withdrawn (removes from stock).
If you have a solution for this that would be excellent!
Unfortunately this doesn't account for additional status changes; each property will almost always have multiple activity types even if it has yet to sell.
I have updated the workbook to include the additional status'. You will see that one of the 'available' properties has now withdrawn, but is still showing in the list.
There is also a property, 8 Victoria Court, which has a 'sale off' activity and need to return to the available stock list.
The available stock list on sheet 3 should read:
1 Bridge Street
8 Main Road
8 Victoria Court
14 Park Road
24 High Street
Can this be achieved?
Thank you Zhouyi, nearly there!
The only thing I've noticed is that if the Listing and Sale entries occur on the same day then it gives a 'True' value. Likewise if the 'Sale off' and 'Withdrawn' entries share the same date.
Other than that all works as expected.