Detectar clientes con valores distintos en un campo para formato condicional

Un miembro de la comunidad tiene una tabla de clientes donde un mismo cliente puede aparecer varias veces. Necesita detectar a simple vista (para usar en formato condicional) aquellos clientes que tienen distintos valores de IR entre sus apariciones. Por ejemplo, si el cliente AL00872 aparece con IR="A" y con IR="B", debe marcarse.

John propone la solución clásica con CONTAR.SI.CONJUNTO:

``
=CONTAR.SI.CONJUNTO(A$2:A$19;A2;C$2:C$19;"<>"&C2)
`

Si devuelve un valor distinto de 0, significa que existe al menos otro registro del mismo cliente con un valor de IR diferente. Directamente utilizable en formato condicional.

Leo ofrece una alternativa matricial con SUMA:

`
=SUMA((A$2:A$19=A2)(C$2:C$19<>C2))
`

Mismo resultado multiplicando dos condiciones booleanas (mismo cliente AND valor distinto). Más flexible para escenarios complejos.

Y la versión más simple de Leo con el operador O:

`
=O((A$2:A$19=A2)(C$2:C$19<>C2))
`

Devuelve directamente VERDADERO/FALSO, perfecto para formato condicional sin necesidad de comparar con >0.

Leo señaló que SUMAR.SI / CONTAR.SI.CONJUNTO no permiten trabajar matricialmente en los rangos de criterio, lo que limita su uso en escenarios más complejos. En esos casos, las fórmulas matriciales con SUMA o el operador O` son más flexibles.

El problema: un mismo cliente con dos valores distintos

Un miembro de la comunidad tiene una tabla de clientes donde un mismo cliente puede aparecer varias veces. Necesita detectar a simple vista —para usarlo en formato condicional— aquellos clientes que tienen valores de IR diferentes entre sus apariciones.

El ejemplo lo deja claro: si el cliente AL00872 aparece una vez con IR igual a "A" y otra vez con IR igual a "B", esa fila debe marcarse. Si todas sus apariciones comparten el mismo valor, no.

Es un control de calidad de datos muy habitual: detectar incoherencias dentro de un mismo grupo antes de que se cuelen en un informe.

Enfoque 1: la vía clásica con CONTAR.SI.CONJUNTO

John propone la solución de toda la vida. La idea es contar cuántos registros comparten cliente pero no comparten valor de IR:

=CONTAR.SI.CONJUNTO(A$2:A$19; A2; C$2:C$19; "@"&C2)

Donde el marcador "@" representa el criterio de desigualdad: en la fórmula real se escribe el operador de "distinto de" entre comillas, concatenado con la celda que se quiere excluir. Es decir, "cuenta las filas cuyo cliente es A2 y cuyo IR es diferente del de C2".

Si el resultado es distinto de cero, existe al menos otro registro del mismo cliente con un valor de IR diferente. Se puede usar directamente como regla de formato condicional, porque cualquier número mayor que cero se interpreta como verdadero.

Enfoque 2: la versión matricial con SUMA

Leo aporta una alternativa que hace lo mismo multiplicando dos condiciones booleanas:

=SUMA((A$2:A$19=A2) * NO(C$2:C$19=C2))

La lectura es directa. La primera comparación devuelve una matriz de verdaderos y falsos según si la fila pertenece al mismo cliente. La segunda, envuelta en NO, devuelve verdadero cuando el valor de IR no coincide. Al multiplicarlas, Excel convierte los booleanos a unos y ceros, y solo sobreviven las filas que cumplen ambas cosas. SUMA las cuenta.

En la comunidad la escribieron usando el operador de "distinto de" en lugar de NO(...=...), pero el resultado es idéntico y esta forma se lee mejor.

Enfoque 3: la versión más limpia con el operador O

Y aquí llega la joya. Si lo único que necesitas es un verdadero o un falso para el formato condicional, ni siquiera hace falta contar:

=O((A$2:A$19=A2) * NO(C$2:C$19=C2))

O devuelve verdadero en cuanto al menos un elemento de la matriz sea distinto de cero. Es exactamente la pregunta que estamos haciendo: "¿existe alguna fila del mismo cliente con otro IR?". Sin comparar con cero, sin envolver en otra función, directo a la regla de formato condicional.

Por qué las matriciales ganan aquí

Leo señaló el matiz importante, y es el aprendizaje que se lleva uno de este caso: SUMAR.SI y CONTAR.SI.CONJUNTO no permiten trabajar matricialmente en los rangos de criterio. El criterio tiene que ser un valor único o una referencia simple.

Mientras el escenario sea sencillo, eso no molesta. Pero en cuanto necesitas que el criterio dependa de un cálculo, de una comparación entre columnas o de otra matriz, esas funciones se quedan cortas. Las fórmulas construidas con SUMA u O sobre matrices booleanas no tienen esa limitación: cada comparación puede ser tan compleja como haga falta.

Funciones clave

  • CONTAR.SI.CONJUNTO — cuenta filas que cumplen varios criterios a la vez. Rápida y legible, pero con criterios poco flexibles.
  • SUMA sobre matrices booleanas — el multiplicador de condiciones. Cada comparación genera verdaderos y falsos, y el producto actúa como un "Y" lógico.
  • NO — invierte una comparación. Alternativa legible al operador de desigualdad dentro de una matriz.
  • O — devuelve verdadero si algún elemento de la matriz lo es. Ideal para formato condicional, que espera un booleano.

Conclusión

Tres caminos para la misma pregunta, con una progresión muy didáctica: de la función especializada a la matriz booleana, y de ahí a la versión mínima que responde exactamente lo que se preguntaba.

Este tipo de comparación dentro del grupo aparece constantemente en la comunidad de InflueXcel: duplicados con datos incoherentes, precios que varían para el mismo artículo, códigos repetidos con descripciones distintas. La técnica de multiplicar una condición de pertenencia por una condición de diferencia sirve para todos ellos.

Más casos con estas funciones

Más contenido de Excel en InflueXcel