Column Q: MSKU – String representing a product
Goal: Count number of days a row item in column Q was out of stock between 7/1/17 and 7/31/17 and pull that number into the row corresponding with that item in Column S
Reference Array: The table Columns D:L represents a report in which each row a daily snapshot is given of a SKU in column F, the warehouse code representing the warehouse in which it is in stock in Column H, the quantity within that warehouse in Column I, and the date from which the data was pulled in Column L.
Problem: Because my items may be in stock in multiple warehouses on any given date, it is challenging to create a formula to count the number of different dates in which an item had stock in any warehouse, as one SKU may appear multiple times in the column corresponding to the same date. The reports used to create the table Column D:L do not show line items without stock – They only have information on products that had stock on the dates recorded. So I cannot use a lookup formula to find dates in which stock was zero, because those dates would not be listed in the column.
It will be a challenge but what I am looking for is a formula is column S that, using the line item in column R, will search the Table (Columns D:L) and find the number of dates between 7/1/17 and 7/31/17 in which no data could be found for the the line item in Column R
Solved by K. J. in 18 mins