SOLVED

Challenge Excel 2

%3CLINGO-SUB%20id%3D%22lingo-sub-1521597%22%20slang%3D%22en-US%22%3EChallenge%20Excel%202%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521597%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20my%20Friends%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20problem%20in%20applying%20a%20formula%20in%20Excel%20%2C%26nbsp%3B%3CBR%20%2F%3EI%20have%20some%20items%20with%20unique%20Cod%2C%20we%20are%20selling%20them%20%2C%20and%20fore%20each%20of%20them%20we%20buying%20specific%20Quantity%2C%26nbsp%3B%3CBR%20%2F%3EI%20need%20a%20formula%20that%20Sum%20all%20the%20selling%20and%20deduce%20it%20from%20Purchase%20and%20give%20me%20the%20remain%20in%20Stock%20for%20each%20item%20with%20specific%20cod%20and%20repeat%20it%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Jalal_1988_0-1594747933743.png%22%20style%3D%22width%3A%20509px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205263i3E8FEE15D8D08BC4%2Fimage-dimensions%2F509x248%3Fv%3D1.0%22%20width%3D%22509%22%20height%3D%22248%22%20title%3D%22Jalal_1988_0-1594747933743.png%22%20alt%3D%22Jalal_1988_0-1594747933743.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CU%3E%3CSTRONG%3EI%20attached%20the%20excel%20file%2C%26nbsp%3B%3C%2FSTRONG%3E%3C%2FU%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20your%20guide%20in%20advance%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1521597%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1521643%22%20slang%3D%22de-DE%22%3ESubject%3A%20Challenge%20Excel%202%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521643%22%20slang%3D%22de-DE%22%3Esry%20i%20don't%20understand%20your%20intentions%20...%20do%20you%20want%20purchase%20to%20automatically%20deduct%20the%20saling%20quantity%3F%20...%20that%20would%20simply%20be%20a%20formula%20in%20the%20row%20of%20floors%3A%20%22%3D%20G8-F8%22%20..%20or%20is%20it%20something%20else%3F%20...%20which%20is%20more%20likely.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1521647%22%20slang%3D%22de-DE%22%3ESubject%3A%20Challenge%20Excel%202%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521647%22%20slang%3D%22de-DE%22%3Esry%20floors%20%3D%20Stock%20row%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1521650%22%20slang%3D%22en-US%22%3ERe%3A%20Challenge%20Excel%202%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521650%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F244734%22%20target%3D%22_blank%22%3E%40Jalal_1988%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20afraid%20I%20do%20not%20completely%20understand%20what%20you're%20asking%20for.%20Perhaps%20you%20could%20manually%20fill%20in%20the%20%22Stock%22%20column%20to%20illustrate%20what%20values%20you%20wish%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20guess%2C%20maybe%20what%20you're%20looking%20for%20is%20something%20like%20the%20following%20in%20the%20first%20row%20of%20the%20%22Stock%22%20column%20(assuming%20your%20table%20starts%20in%20cell%20A1)%3A%3C%2FP%3E%3CP%3E%3D%24D2-SUMIFS(%24C%242%3A%24C%2421%2C%24A%242%3A%24A%2421%2C%24A2%2C%24B%242%3A%24B%2421%2C%24B2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1521675%22%20slang%3D%22en-US%22%3ERe%3A%20Challenge%20Excel%202%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521675%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BI%20manually%20filled%20the%20Stock%20column%20for%20your%20understanding%2C%20Purchase%20column%20is%20repeated%20for%20each%20Cod%20and%20item%2C%20I%20need%20formula%20that%20Deduce%20the%20Sum%20of%20selling%20from%20Purchase%2C%20and%20give%20it%20in%20Stock%20...%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1521715%22%20slang%3D%22en-US%22%3ERe%3A%20Challenge%20Excel%202%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521715%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F244734%22%20target%3D%22_blank%22%3E%40Jalal_1988%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreat%2C%20understood.%20Thank%20you!%20In%20that%20case%2C%20is%20the%20formula%20in%20my%20previous%20post%20similar%20to%20what%20you're%20looking%20for%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1521754%22%20slang%3D%22en-US%22%3ERe%3A%20Challenge%20Excel%202%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521754%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20about%20this%3F%3C%2FP%3E%3CP%3EIn%20H2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DABS(SUMIFS(%24F%248%3A%24F%2427%2C%24D%248%3A%24D%2427%2CD8%2C%24E%248%3A%24E%2427%2CE8)-MAXIFS(%24G%248%3A%24G%2427%2C%24D%248%3A%24D%2427%2CD8%2C%24E%248%3A%24E%2427%2CE8))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20then%20copy%20it%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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, 


Jalal_1988_0-1594747933743.png

 


I attached the excel file, 

Thank you for your guide in advance 

6 Replies
sry i don't understand your intentions ... do you want Purchase to automatically deduct the saling quantity? ... that would simply be a formula in the row of floors: "= G8-F8" ..or is it something else? ... which is more likely.


Nikolino
I know I don't know anything (Socrates)
sry floors = Stock row

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)

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 ...

   

@Jalal_1988 

 

Great, understood. Thank you! In that case, is the formula in my previous post similar to what you're looking for?

Best Response confirmed by Jalal_1988 (Contributor)
Solution

@PReagan 

How 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.