Forum Discussion

Christine_D1973's avatar
Christine_D1973
Copper Contributor
Sep 05, 2025

Excel - Matching outgoing to incoming inventory based a specific conditions

Hello,   I wonder if anyone here can help me.  I have created an inventory tracking file in excel but I have encountered a difficulty that I cannot seem to find an answer for. I have sequenced eac...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Sep 06, 2025
=IF(SUMIFS($H$2:$H$1000,$D$2:$D$1000,D2)+SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)=0,"closed",
IF(SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)=0,"open",
IF(SUMIFS($H$2:$H$1000,$D$2:$D$1000,D2)+SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)>0,"in progress","")))

This is the formula in cell G2.

=IF(A2<>"","",INDEX($A$2:A2,LARGE(IF($A$2:A2<>"",ROW($A$1:A1)),1)))

This is the formula in cell B2. The formula must be entered as an arrayformula with ctrl+shift+enter if you work with legacy Excel such as Excel 2013.

 

Both formulas must be filled down.

 

  • Christine_D1973's avatar
    Christine_D1973
    Copper Contributor
    Sep 08, 2025

    Hello Oliver,

    I copied the formulas into my file - one worked (Status), one did not ("Out" sequence)

    I get a #REF! error when I insert the following formula:

    =IF(D7<>"","",INDEX($D$7:D7,LARGE(IF($D$7:D7<>"",ROW($D$6:D6)),1)))

    I can't seem to attach a file so here is a snip, would you mind taking a look?

    Again, thank you for your help!

     

    CHristine

     

  • Christine_D1973's avatar
    Christine_D1973
    Copper Contributor
    Sep 08, 2025

    Hello Oliver,

     

    Thank you very much for your help.

    I have inserted both formulas into my file, The "Status" formula works, the "Out" sequence does not.

    I get a #REF! error when I insert the formula:

     IF(D7<>"","",INDEX($D$7:D7,LARGE(IF($D$7:D7<>"",ROW($D$6:D6)),1)))

    I can't seem to attach a my file so here id a "Snip" of my file. 

    Would you mind taking a look ? (Column "E")

    Again, thank you for your help.

     

     

    Christine

     

     

     

     

Resources