Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have 3 part numbers with the same part name xyz, each with a quantity of 10 items. The items can be picked during the day or week, therefore changing the amount of items on hand.

I know I need to use the group by, sort, count and perhaps sumif formulas to have a running count of the number of items on hand at the end of each day (which could be positive or negative).

share|improve this question
add comment

1 Answer

This is a little hard to explain, and would be a lot easier if I could just attach the spreadsheet. But I'll give it a go with a screen shot (pardon if it looks out of place, my job's network filters it out >:0 ).

=INDEX($D$2:$H$4,MATCH(B$7,$B$2:$B$4,0),MATCH($A8,$D$1:$H$1,0))

Screenshot

The top table can be setup however you want. The important items are the part numbers and the Days. The lower table uses the formula above to get the Row and Column of the Part Number and Day and finds the Index (or intersection) of that Row and Column pair within the upper table.

Stepping through the formula:
$D$2:$H$4 is the table of daily sales for the three parts
The first MATCH() finds the index (or row) of B$7 (or part 123) in the array $B$2:$B$4
The second MATCH() finds the index (or column) of $A8 (of DayOne) in the array $D$1:$H$1
Thus, INDEX() finds the cell at the intersection of the first MATCH() (row) and the second MATCH() (column) in the table $D$2:$H$4

Finally, a little additional subtraction in the lower table will give you daily remaining inventory (not shown in the formula above to keep it simpler).

Hopefully this is understandable.

share|improve this answer
    
This looks great, but perhaps showing you an actual example of what I am looking for in our format can help explain myself better? Regardless, I really appreciate your help!! –  Chris Nov 1 '10 at 21:02
    
D:\Documents and Settings\204051799\My Documents\EXCEL_EXPLANATION.png –  Chris Nov 1 '10 at 21:03
    
sorry, screen shot didn't work. do you know how to attach screenshots here? –  Chris Nov 1 '10 at 21:06
    
i believe you would need to add (edit) it to your original question. when posting answers there is a button near the top to insert an image –  Xantec Nov 1 '10 at 21:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.