Jul 14 2020 10:36 AM
Hello my Friends
I have problem in applying a formula in Excel ,
I have some items with unique Cod, we are selling them , and fore each of them we buying specific Quantity,
I need a formula that Sum all the selling and deduce it from Purchase and give me the remain in Stock for each item with specific cod and repeat it,
I attached the excel file,
Thank you for your guide in advance
Jul 14 2020 10:51 AM
Jul 14 2020 10:53 AM
Hello @Jalal_1988,
I'm afraid I do not completely understand what you're asking for. Perhaps you could manually fill in the "Stock" column to illustrate what values you wish to achieve.
As a guess, maybe what you're looking for is something like the following in the first row of the "Stock" column (assuming your table starts in cell A1):
=$D2-SUMIFS($C$2:$C$21,$A$2:$A$21,$A2,$B$2:$B$21,$B2)
Jul 14 2020 11:03 AM
Hello @PReagan
I manually filled the Stock column for your understanding, Purchase column is repeated for each Cod and item, I need formula that Deduce the Sum of selling from Purchase, and give it in Stock ...
Jul 14 2020 11:30 AM
Great, understood. Thank you! In that case, is the formula in my previous post similar to what you're looking for?
Jul 14 2020 11:42 AM
SolutionHow about this?
In H2
=ABS(SUMIFS($F$8:$F$27,$D$8:$D$27,D8,$E$8:$E$27,E8)-MAXIFS($G$8:$G$27,$D$8:$D$27,D8,$E$8:$E$27,E8))
and then copy it down.
Jul 14 2020 11:42 AM
SolutionHow about this?
In H2
=ABS(SUMIFS($F$8:$F$27,$D$8:$D$27,D8,$E$8:$E$27,E8)-MAXIFS($G$8:$G$27,$D$8:$D$27,D8,$E$8:$E$27,E8))
and then copy it down.