Forum Discussion

Excel's avatar
Excel
Iron Contributor
Jul 27, 2022
Solved

Query related to formula

Hello Everyone, 

 

I want to check the left over stock for the specific fruit name's Serial number. So example, at the start I have 5 Apples of serial number 123,     2 Apples check out which left 3 Apples left, but a new stock of oranges came in for example, 1 Apple of serial number 123, therefore my current available stock for Apples would be 4.

 

Please help..

 

Here is a attached file..

excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Jul 27, 2022

    Excel 

     

    So what you need is to add check-in fruits with existing stock then minus checkout quantities. You have to use SUMIFS() function. Try below formula-

    =D3+SUMIFS($Q$3:$Q$50000,$P$3:$P$50000,C3,$N$3:$N$50000,A3)-SUMIFS($L$3:$L$50000,$K$3:$K$50000,C3,$I$3:$I$50000,A3)
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Jul 27, 2022

    Excel 

     

    So what you need is to add check-in fruits with existing stock then minus checkout quantities. You have to use SUMIFS() function. Try below formula-

    =D3+SUMIFS($Q$3:$Q$50000,$P$3:$P$50000,C3,$N$3:$N$50000,A3)-SUMIFS($L$3:$L$50000,$K$3:$K$50000,C3,$I$3:$I$50000,A3)

Resources