hi i am pretty new to excel and am having problems with this formula. I would appreciate anyone's time on the following.
What i am trying to achieve is to keep a running total of books although some of them have the same STN number as they are the same book ie
STN........ ISSUED............ QTY RECEIVED ........... STOCK LEVEL
23......... .. 2.......................... - .........................6
23 ......... 2.......................... - .........................4
34 ............1.......................... - ........................ 3
56............ 2......................... - .........................15
34.............-......................... 5.......................... 8
23.............-........................ 4.......................... 8
The problem i am having is i can get the formula to total up the SN using =SUMPRODUCT((B4:B103>=B4)*(B4:B103<=B4)*(C4:C103))but for example (SN23 current stock level is 8 but i would like to use one stock level per SN. at the moment i cannot identify how many of which SN i have on stock.
is there any way i could keep a running total for example if i was to receive 3 books of SN23 again for a cell to tell me exactly the total remaining is. at the moment i have 3 different stock levels for SN23.
ideally if possible a cell i would like to use this another sheet when i loan the books to other departments a method of looking at my issues workbook and seeing exactly how much i have for that particular SN.
STN........ ISSUED............ QTY RECEIVED ........... STOCK LEVEL
23......... .. 2.......................... - .........................6
23 ......... 2.......................... - .........................4
34 ............1.......................... - ........................ 3
56............ 2......................... - .........................15
34.............-......................... 5.......................... 8
23.............-........................ 4.......................... 8
23............ -......................... 3......................... 11 <, new entry
i have tried to explain it but it is probably as clear as mud.
thank you for your time in reading this anyway
What i am trying to achieve is to keep a running total of books although some of them have the same STN number as they are the same book ie
STN........ ISSUED............ QTY RECEIVED ........... STOCK LEVEL
23......... .. 2.......................... - .........................6
23 ......... 2.......................... - .........................4
34 ............1.......................... - ........................ 3
56............ 2......................... - .........................15
34.............-......................... 5.......................... 8
23.............-........................ 4.......................... 8
The problem i am having is i can get the formula to total up the SN using =SUMPRODUCT((B4:B103>=B4)*(B4:B103<=B4)*(C4:C103))but for example (SN23 current stock level is 8 but i would like to use one stock level per SN. at the moment i cannot identify how many of which SN i have on stock.
is there any way i could keep a running total for example if i was to receive 3 books of SN23 again for a cell to tell me exactly the total remaining is. at the moment i have 3 different stock levels for SN23.
ideally if possible a cell i would like to use this another sheet when i loan the books to other departments a method of looking at my issues workbook and seeing exactly how much i have for that particular SN.
STN........ ISSUED............ QTY RECEIVED ........... STOCK LEVEL
23......... .. 2.......................... - .........................6
23 ......... 2.......................... - .........................4
34 ............1.......................... - ........................ 3
56............ 2......................... - .........................15
34.............-......................... 5.......................... 8
23.............-........................ 4.......................... 8
23............ -......................... 3......................... 11 <, new entry
i have tried to explain it but it is probably as clear as mud.
thank you for your time in reading this anyway
Last edited: