Formula to Identify Duplicates and Get Count

[legalhustler]

Hello,

I need an Excel formula to find duplicates based on two columns (Region/Station) and return the count in the last occurrence. For example, there two duplicates of North/40 so the last occurrence shows 2, likewise there is 4 duplicates for East/20 so the result in the last occurrence shows 4. The ones that are unique should be empty/blank. Please help.

Region	Station	Result
East	20
North	40
South	50
East	20
North	40	2
East	20
East	20	4

 

[Joe4]

Works well for a regular data set like the above, but I tried to use it without the LET function (for ease of reading and evaluation) for a Excel table but it gives me the count for each row instead for the last occurrence.

Not sure what you mean by "ease of reading". Using the LET function eliminates redundancy to make the formula shorter and easier to read!
My same formula without using the LET function would look like this:

=IF(COUNTIFS(A:A,A2,B:B,B2)=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2),COUNTIFS(A:A,A2,B:B,B2),"")

You can see how this is shorter and not as hard to read:

=LET(x,COUNTIFS(A:A,A2,B:B,B2),IF(x=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2),x,""))

If you do not understand how the LET function works, see here: LET function - Microsoft Support