Question: I usually export the stock movement data from our Accounting Package into Excel. However I struggle to calculate the total for the negative values (stock out) and positive values (stock in) separately .The data is placed in one column when exported to Excel. Is there a way of overcoming the allocation of positives and negatives to the stock movement?
Answer: Yes, with the SumIf function. In Excel, the SumIf function adds all numbers in a range of cells, based on a given criteria.
Why: To calculate the total of the stock movement, (stock in and stock out) data separately.
Applies To:
Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
1. Enter data as in the example given below
2. Select cell E13 and type =SUMIF(E4:E12,”>0”)
3. Select cell E14 and type =SUMIF(E4:E12,”<0”)
4. The result will be as below
One is therefore able to calculate the stock in and stock out totals by using the SUMIF function.
