1, the number of products in stock, by the number of incoming and outgoing quantities summarized, so the inventory table should contain "inbound", "outbound" information, in order to facilitate the query, "Date "information is also not missing.
2, and then enter the relevant records, the number of inventory from the last day (the next) inventory plus the number of days in minus the number of out of stock, this data does not need to be manually calculated, you can use the formula by the software directly from the results of the formula entered in the D3: = SUM (D2, B3) - C3.
3, you can not add the number of day's inventory, and the number of total inventory minus the total number of out of stock Quantity of the current inventory: = SUM (B $ 3:B3) - SUM (C $ 3:C3), at this point it is important to note that the beginning of the summation region of the line should be added to the absolute references $, that is, always from the first line to the current line of data for the summation.
4, if in addition to the normal number of outlets there is loss, you can also increase the loss of a column, summing, the loss of statistics can be included: = SUM (E2,B3) - SUM (C3:D3).
5, if it is a few products, you can side-by-side columns, respectively, to find their respective inventories, due to the formula is used in the relative reference, and reference to the relative position of the region is identical, so you can copy the formula directly from the front over to paste it, it will automatically be changed to the target cell formula: = SUM (G3,E4) - F4.
6, in order to facilitate the distinction between the between different products to change the border line, such as double lines to separate.
7, but if there are more products, horizontal arrangement is not very convenient, you can arrange the products vertically, the date is changed to horizontal, according to the corresponding products to enter the inventory data.
8, and then use the formula to calculate the total number of warehouses: = SUMIF (E $ 3: CG $ 3, "warehousing", E4: CG4), the formula in the third line to add an absolute reference so that the downward padding will always refer to the third line of the column header area.
9, the total number of out-of-stock formula with the total number of inventory is similar, only the formula "in" to out of stock: = SUMIF (E $ 3:CG $ 3, "out of stock", E4:CG4).
10, with the total amount of inventory and the total amount of inventory, the two numbers can be reduced to get the number of inventory, if there is a loss of the number, you can also add another column, the formula is similar. Inventory formula: = C4 - D4.
11, in order to increase the date, the front of the summary of the number of always visible, you can freeze the pane, select the E column, click View - Freeze Pane, Freeze Split Pane, you can freeze the front of the summary of the part of the freeze, to the right when dragging the scroll bar, will only hide the front of the date. You can also "in" and "out" placed separately, and then use hyperlinks to locate the specified location, fast data entry, very convenient.