Ranking values and avoid repeated numbers more accuracy

May I propose a simpler solution - using PivotTable instead of formulas.

First, I have formatted as tables the data in columns A to E in both the Stock Balance Code and Stock Sold Code sheets. This is to ensure any new data added will automatically be picked up in PivotTables.

Then insert PivotTable based on either table, remember to tick the "Add this data to the Data Model". Insert Slicer for Category.

Remember to hit [Refresh] (right-click inside the PivotTable > Refresh) when new data are added.

Note: I have cleaned up your workbook as the file size exceeded the forum's limit of 1MB.