Forum Discussion
Re: Excel - Matching outgoing to incoming inventory based a specific conditions
=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.
2 Replies
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
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
