Contar coincidencias entre dos rangos

En este ejemplo se revisa como contar coincidencias entre dos rangos, para ello se revisarán dos posibles soluciones que mostramos a continuación.

Fórmula para contar coincidencias entre dos rangos

En la celda F3 podemos encontrar la fórmula:

=SUMAPRODUCTO(--(B3:B7=D3:D7))

Esta parte de la fórmula (B3:B7=D3:D7) , realiza la comparación una a una las celdas de cada uno de los dos rangos, en el mismo orden en que aparecen, es decir primero se compara B3 con D3 luego B4 con D4, si ambas son iguales (sin importar las diferencias entre mayúsculas y minúsculas) se retornará el valor VERDADERO, si por el contrario son distintas se retornará FALSO, para efectos del ejemplo la lista de valores que se obtienen son los siguientes:

{VERDADERO, FALSO, FALSO, FALSO, FALSO}

Luego el operador doble guión "--", sirve para cambiar estos valores  en "1"  si se tiene un valor VERDADERO, y "0" si el valor es FALSO, por lo que la lista quedaría de la siguiente forma:

{ 1 , 0, 0, 0, 0}

Finalmente SUMAPRODUCTO nos retornará la suma de estos valores, por ello el resultado es "1".

Fórmula para contar coincidencias entre dos columnas

De forma similar a la fórmula anterior se puede extender la comparación para toda la columna, sin embargo al hacer esto las celdas vacías de ambas columnas también contarán como parte de las coincidencias, para evitar este efecto es necesario añadir la condición (D:D<>"") por medio de la cual se excluirían estas celdas. La fórmula con esta corrección quedaría de la siguiente forma:

=SUMAPRODUCTO((B:B=D:D)*(D:D<>""))

Contar las coincidencias (sin importar la fila)

En el ejemplo mostrado en los párrafos anteriores, se obtiene 1 debido a la coincidencia existente en la fila 3, donde B3 y D3 contienen el texto "Juan", sin embargo "Esteban" que se encuentra en la celda B4 y D5, es decir que esta en ambas listas, no forma parte del conteo debido a que el texto se encuentra en dos listas distintas. Veamos como hallar una solución par este caso de forma sencilla:

En la celda F3 se utilizó la fórmula:

=SI(ESERROR(BUSCARV(D3,$B$3:$B$7,1,FALSO)),0,1)

Esta fórmula permite identificar si el valor que contiene D3 se encuentra en el rango B3:B7, si el valor buscado es encontrado se retornará un valor en caso contrario se obtendrá un error, es por ello que se empela la función ESERROR en combinación con la función SI, se esta forma si existe un error (cuando el valor no exista) la función SI retornará "0", mientras que cuando el valor buscado si encuentre la función retornará el valor "1".

Esta fórmula solo permite determinar la existencia de D3 o no en el otro rango, por ello será necesario copiar esta formula a las celdas  F4 y F5,  de esta forma podremos identificar todos los valores de la lista 2 que existen en la lista 1, si se requiere identificar la cantidad de coincidencias solo restaría añadir una sumatoria de los valores previamente obtenidos con algo como =SUMA(F3:F5)