Counting when multiple revisions of an item are present

[chullan88]

Hi,

I have a material log and each material is assigned a status ( either A,B,C, or D).
I need to count the no. of materials with each type of status ( No of items with status A,B etc)
The catch is that the materials are duplicated and are arranged according to their revisions ( The item " Concrete" when appear in the log first time is identified as 1st revision and if appear again as 2nd revision and so on)
I need to consider only the highest revision of each item while counting the no. of items with each status
Eg. If "Concrete" 1st revision is Status "A" and 2nd revision is Status "B"
for concrete, while counting only Status B will be considered

I have around 5k+ rows and need a solution which would be quick

Thanks in advance