@Excel Enthusiast
You seem to be using Tables but, as yet, have made little use of Structured Referencing.
If one considers the column 'No. of Bags' it appears to be manually input data so it cannot be changed by a formula. What one could do is define a named formula that will take the absolute value from the number of bags entry and the sign from the 'Status' field. This can be done by creating the name '
No.ofBags' in Name Manager and letting it refer to
= IF(Table1[Status]="Pass", 1, -1) * ABS(Table1[No. of Bags])
Then, the formula
= SUM(No.ofBags)
will take deductions into account.
There are further 'tricks' that can be played but the danger is that you will sometime in the future forget and catch yourself out. As it stands, the count of bags to be deducted still shows as positive despite the fact that you intend to subtract them. It is possible to use Conditional Formatting to show the number preceded by a minus sign, despite the fact that it was entered as positive. The condition would be
=$N4="Reject"
and the number format would be
-0;-0;;@
To hide any negative numbers input by the user within these columns, one could use the default number format
0;0;;@