Fórmula genérica
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Resumen
Para crear una tabla de resumen con un recuento de elementos emparejados que aparecen en una lista de combinaciones existentes, puede usar una columna auxiliar y una fórmula basada en la función CONTAR.SI. En el ejemplo que se muestra, la fórmula en la celda H5 es:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
donde "ayudante" es el rango con nombre E5: E24.
Nota: esta fórmula asume que los elementos no se repiten en una combinación determinada (es decir, AAB, EFE no son combinaciones válidas).
Explicación
Queremos contar la frecuencia con la que los elementos de las columnas B, C y D aparecen juntos. Por ejemplo, la frecuencia con la que A aparece con C, B aparece con F, G aparece con D, y así sucesivamente. Esto parecería un uso perfecto de COUNTIFS, pero si intentamos agregar criterios buscando 2 elementos en 3 columnas, no funcionará.
Una solución alternativa simple es unir todos los elementos en una sola celda y luego usar COUNTIFS con un comodín para contar elementos. Lo hacemos con una columna auxiliar (E) que une elementos en las columnas B, C y D usando la función CONCAT de esta manera:
=CONCAT(B5:D5)
En versiones anteriores de Excel, puede usar una fórmula como esta:
=B5&C5&D5
Debido a que no se permiten elementos repetidos en una combinación, la primera parte de la fórmula excluye los elementos coincidentes. Si los dos elementos son iguales, la fórmula devuelve un guión o un guión como texto:
=IF($G5=H$4,"-"
Si los elementos son diferentes, se ejecuta una función CONTAR.SI:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS está configurado para contar "pares" de elementos. Solo cuando el elemento de la columna G y el elemento correspondiente de la fila 4 aparecen juntos en una celda, se cuenta el par. Se concatena un comodín (*) a ambos lados del elemento para garantizar que se cuente una coincidencia sin importar dónde aparezca en la celda.
