# Group By, Sort, Count functions to keep running total

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).

-

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))
``````

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.

-
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