Count ifs formula

[duckworj]

hi - First post in a very long time - I'm wondering if someone could help please?
I am trying to work out how we can establish our stock levels

Each row would identify either a customer order - using columns for customer name and product code or an item in stock using the term "stock" and product code.
I have 6 columns to identify various stages of stock
L - Delivered and allocated to customer (or allocated to available stock)
M - Collected by customer
P - Returned for Refurbishment
Q - Date Refurbished
R - Date reallocated to available stock (Ideally the item would be entered on a new row with a date entered in column L - but by putting the date in column R we can identify if this needs to be done or not.)
S - stock damaged or lost

Stock moves through the process as follows - L, M,P,Q, R and then L again if stock has been refurbished or L, M, S if stock is damaged and unable to be refurbished.
I am able to count the levels of each item of stock using count ifs formulas for the majority of the stages (by entering dates into the relevant columns), but I cannot figure out what formula I should use to establish how much stock we have available (stock allocated to customers not yet collected, plus stock not yet allocated to customers (using columns L and R as the very minimum).

Can anyone help please - I seem to be going round and round in circles.

Thanks

roundabout

 

[Anfinsen]

Hi Duck,
You would likely be more successful in obtaining help, if you posted an example sheet using XL2BB.

 

[Herakles]

hi - First post in a very long time - I'm wondering if someone could help please?
I am trying to work out how we can establish our stock levels

Each row would identify either a customer order - using columns for customer name and product code or an item in stock using the term "stock" and product code.
I have 6 columns to identify various stages of stock
L - Delivered and allocated to customer (or allocated to available stock)
M - Collected by customer
P - Returned for Refurbishment
Q - Date Refurbished
R - Date reallocated to available stock (Ideally the item would be entered on a new row with a date entered in column L - but by putting the date in column R we can identify if this needs to be done or not.)
S - stock damaged or lost

Stock moves through the process as follows - L, M,P,Q, R and then L again if stock has been refurbished or L, M, S if stock is damaged and unable to be refurbished.
I am able to count the levels of each item of stock using count ifs formulas for the majority of the stages (by entering dates into the relevant columns), but I cannot figure out what formula I should use to establish how much stock we have available (stock allocated to customers not yet collected, plus stock not yet allocated to customers (using columns L and R as the very minimum).

Can anyone help please - I seem to be going round and round in circles.

Thanks

roundabout

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.