Counting days in inventory by item

QuestionEdit

Using2007, I have 1200+ items in inventory in Column A (each has its own ID#) and the monthly inventory date in Column B. This returns a current total of 9581 entries. I need to know how many days each item has been in stock.

Without having to manually input Days360 after each change in ID#, is there a formula to automatically count days in inventory and insert at each change in ID#?

I copied and removed all duplicate ID #'s and pasted below the data, starting in Column A Row 9586, and tried =Days360($A$S:$A$9581,A9586,$B$2:$B:9581without luck also.

AnswerEdit

Toni,

do you absolutely need to have the number at the change of ID#?

If not, you could get a listing with a pivot table as I show in the attached picture.

If you need it at the juncture you could put in a formula like

C2: =IF(A2<>A3,MAX(IF($A$2:A2=A2,$B$2:B2))-MIN(IF($A$2:A2=A2,$B$2:B2)),"")

this must be entered with Ctrl+shift+enter rather than just enter since this is an array formula. In the picture you see it appears enclosed in curly brackets. I entered the formula as written and entered it as explained. displays the curly brackets to indicate that it is being interpreted as an array formula. if the results appear as a weird date, then just format the cell as general and you will get an integer number as expected.

Once entered properly, then select C2 and drag fill it down the column. So the picture shows two ways to get the information.

the picture shows my assumption (based on your description in the question) on how your data is laid out. My formula solution is based on that assumption.

If you have problems, send me a sample workbook with a representative subset of your data and tell me what problem you are having and I will try to advise.