Count the number of consecutive weeks that an item was out of stock in 1 year

**tftjr** · 05-29-2013, 07:17 AM

Hello All,
Please help if you can.

I have a spreadsheet with a years-worth of data showing how many times an item was out of stock. However, now my boss wants to know how many weeks in a row that item was out of stock.

I have a unique Stock Number, Total Cases Out of Stock, Week # (as in this week is the 22nd week of 2013), Month and Warehouse (we have 4 warehouses). It is formatted starting from May 2012 until now with the oldest information at the top and descending by week # from there. An item can be out of stock multiple times in one week because we have multiple warehouses so that is where the problem lies.

I'm not sure what other info you may need.
Thank you,
Terry

**XOR LX** · 05-29-2013, 07:24 AM

"I'm not sure what other info you may need."

A sample spreadsheet.

Regards

**tftjr** · 05-29-2013, 07:32 AM

File attached

**XOR LX** · 05-29-2013, 01:57 PM

Hi,

I'm just trying to work out the logic of your sheet before getting into any formula-work. One of the things which is obviously necessary to know is when an item - which has been out of stock - is then back in stock.

I thought at first that this would be indicated by a zero in the Total Cases OUT OF STOCK column, but then I noticed that your formula in column G (which judging from the header is giving the total number of times which an item was not in stock) is calculated by adding up ALL instances of each item in the sheet, whatever the number in column C (i.e. including all those with a zero.)

Can you please clarify?

Regards