We have a 3-stage shipment process. Have a look at the below screenshot to understand how the data is captured in the shipment tracker excel report.
Based on each stage status, the consignments are categorized in two main categories. They are Category-A and Category-B.
Criteria to decide a consignment’s category is listed below:
Category – A -> Stage 1 should contain the word “pending” and for stage 2 and stage 3 if both of them contains the word “pending” or either one of them contains the word “pending”.
Category – B -> All except the category – A will go under this bucket.
We need to use sumproduct formula along with any other formula which will give us how many consignments goes under Category-A and how many under Category-B.
Download the excel file by clicking here
You can post your solution in the comments section