=IF(LEFT(D2,10)="Cancel PO",IF(COUNTIFS($D$2:$D$10,"New P/O",$E$2:$E$10,E2)>1,-SUMIFS(MRPExport!L:L,MRPExport!D:D,D2,MRPExport!E:E,E2)),SUMIFS(MRPExport!L:L,MRPExport!D:D,D2,MRPExport!E:E,E2))
=IF(D2="New P/O",ABS(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$P$2:$P$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$P$2:$P$10)),IF(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$P$2:$P$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$P$2:$P$10)<0,0,J2*P2))
=IF(AND(D2="New P/O",(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10))<0),0,IF(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)<0,0,I2*N2))
=IF(AND(D2="New P/O",(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10))<0),0,IF(D2="New P/O",ABS(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)),IF(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$I$2:$I$10,$N$2:$N$10)<0,0,I2*N2)))
=IF(AND(D2="New P/O",(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10))<0),0,IF(D2="New P/O",ABS(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)),IF(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)<0,0,J2*N2)))
=IF(AM2=0,0,IF(AND(D2="New P/O",(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10))<0),0,IF(D2="New P/O",ABS(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)),IF(SUMPRODUCT(--($D$2:$D$10="New P/O"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)-SUMPRODUCT(--(LEFT($D$2:$D$10,6)="Cancel"),--($E$2:$E$10=E2),$J$2:$J$10,$N$2:$N$10)<0,0,J2*N2))))
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
VBA Help | 18 | 44 | |
Dynamic control of Items in an Excel multiListBox | 7 | 29 | |
which one of the last argument of YEARFARCis correct to use | 5 | 23 | |
Excel Formula to check both condition's and return values | 2 | 28 |
Join the community of 500,000 technology professionals and ask your questions.