Crear número consecutivo en excel dependiente de un valor

Tengo una tabla como la siguiente:

A B C D

Cuernavaca CVA 1 CVA-1

Cuernavaca CVA 2 CVA-2

Cuernavaca CVA 3 CVA-3

Cuautla CUA 1 CUA-1

Cuernavaca CVA 4 CVA-4

En la columna A tengo el listado de Ciudades, en la B, un listado de prefijos que se llenan automáticamente dependiendo de la ciudad de la columna A.

En la columna C, quiero poner el consecutivo, pero que se llene en automático, es decir, que vea cual fue el ultimo valor por ejemplo de CVA, y si escribo una linea mas y en ciudad pongo Cuernavaca, que el numero sea 5, pero si pongo Cuautla, el numero sea 2.

En la columna D, concatenare estos dos valores de las columnas B y C, para generar el código que necesito para seguimiento.