Forum Discussion
Excel - Matching outgoing to incoming inventory based a specific conditions
Hi Oliver :)
The formula you have given me is complicated beyond my ablities... but I was able to understand that you were referencing specific columns so I managed to adjust the formula in my file to grab the correct columns. And.... Eureka.... it works :)
Can we circle back to the formula for the "Status"? The previous formula was (which I understand it to be based on Supplier PO only):
=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","")))
I need the status to be determined by Seq no. / Supplier PO / SKU - I boxed in "PINK" to show what goes together and what the status should be.
Even though PO 1276348 repeats itself several times with several different products, the intention for "STATUS" is to "close" out when and incoming sequence # and outgoing sequence # net out to 0 for a specific product in question - Is this clear?
So, incoming # 1 and outgoing # 1 net out to 0 for 1276348 / A2620 SM BLU - "closed"
So, incoming # 2 and outgoing # 2 for 1276348 / A2620 SM BLU - "in progress"
So, incoming # 1 and outgoing # 1 for 1276348 / A2620 SM GRY - "in progress"
and so on...
Again, thank you!
Christine :)
Hello Christine,
thank you for your detailed explanation. I understand the task and the formula in cell K7 returns the results. The formula must be filled down. In the screenshot i've hidden some rows and highlighted 3 unique combinations of "Supplier PO" and "SKU" for illustration.
I've attached the sample file with 2 sheets with hidden and unhidden rows of the sample database.
Regards,
Oliver
=IF(
OR(
AND(SUMIFS($V$7:$V$1000,$D$7:$D$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)
+SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)=0,
SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0),
AND(SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)+V7=0,
SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0)),
"closed",
IF(
OR(SUMIFS($V$7:$V$1000,$D$7:$D$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)
+SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<>0,
AND(SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)+V7<>0,
SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0)),
"in progress",
"")
)
Hi Oliver :)
You've been great!!! I thank you for your help, and patience. This works.
A happy and satisfied user :)
Christine