CONTAR.SI no acepta ELEGIRCOLS: por qué las funciones .SI exigen una referencia y no una matriz

Hay errores de Excel que te mandan a buscar en la dirección equivocada, y este es de manual. Joan Recasens llega al grupo con una fórmula que llevaba semanas usando sin problema:

``
=CONTAR.SI.CONJUNTO(ELEGIRCOLS('TOP 3 analysis'!$A:$CY;4);"")
`

Y lo que Excel le devuelve no es un #¡VALOR! ni nada que oriente, sino el cuadro de diálogo genérico: "Hay un problema con esta fórmula. ¿No intenta introducir una fórmula? Cuando el primer carácter es un signo igual (=) o un signo menos (-), Excel piensa que se trata de una fórmula...", con su consejo sobre poner un apóstrofo delante. Un mensaje que habla de signos igual y apóstrofos cuando el problema no tiene nada que ver con ninguna de las dos cosas. De ahí la pregunta con la que abre el hilo: "hace unas semanas que este tipo de expresiones no funcionan, ¿puede ser?".

John da con la causa en un par de líneas: ELEGIRCOLS no devuelve una referencia, devuelve una matriz. Y el argumento de rango de criterios de las funciones de la familia .SI (CONTAR.SI, CONTAR.SI.CONJUNTO, SUMAR.SI.CONJUNTO) no admite matrices: exige una referencia real a celdas. No es que la fórmula esté mal escrita, es que ese hueco solo acepta un tipo de dato que ELEGIRCOLS nunca produce.

Su alternativa es quedarse en el terreno de las referencias, y para eso INDICE tiene una propiedad que se usa poco: si le omites el argumento de fila, devuelve la columna entera como referencia.

`
=CONTAR.SI(INDICE('TOP 3 analysis'!$A:$CY;;4);"")
`

Leo confirma el diagnóstico y añade la segunda mitad del problema, que es la que explica por qué aquí no vale cualquier apaño. El "" del criterio no significa lo mismo según dónde esté: en una comparación directa (A1="") es un asterisco literal, un carácter y nada más; dentro de CONTAR.SI, SUMAR.SI o BUSCARV pasa a comportarse como comodín, de modo que "abc" significa "cualquier texto que acabe en abc". Como Joan está contando celdas no vacías apoyándose precisamente en ese comportamiento, la solución tiene que conservarlo, y por eso INDICE encaja mejor que sustituir la lógica por otra cosa. Leo deja además la variante con la función volátil de la casa:

`
=CONTAR.SI(DESREF('TOP 3 analysis'!A1;;3;2^20);"")
`

Hugo Barreto ataca por el otro lado: en vez de buscar una referencia para poder seguir usando CONTAR.SI, cambia de función y usa una que sí digiere matrices sin protestar.

`
=SUMAPRODUCTO(--(ELEGIRCOLS(J1:M11;3)>""))
`

Aquí la doble negación convierte los VERDADERO/FALSO en unos y ceros, y SUMAPRODUCTO los suma. Ojo a la diferencia de fondo entre los dos caminos: este no cuenta con comodines, cuenta celdas cuyo contenido es mayor que texto vacío, así que el resultado puede no ser idéntico según qué haya en la columna.

Miki apunta un tercer enfoque en la misma línea, con FILTRAR, que también trabaja con matrices:

`
=FILAS(FILTRAR(ELEGIRCOLS();ELEGIRCOLS()=""))
`

La regla que sale de todo esto es fácil de recordar y ahorra muchos ratos: las funciones de la familia .SI piden referencias; el resto de funciones modernas trabajan con matrices. Si una función devuelve una matriz calculada (ELEGIRCOLS, ELEGIRFILAS, FILTRAR, ORDENAR, APILARV), no la metas en el hueco del rango de un CONTAR.SI. O consigues una referencia de verdad con INDICE o DESREF, o cambias a una función que acepte matrices como SUMAPRODUCTO.

Ampliación (30 de agosto)

Con el caso ya publicado, Leo vuelve al hilo con un tercer camino para conseguir una referencia, y este no necesita ni el argumento omitido de INDICE ni la volatilidad de DESREF: TOMAR también devuelve referencias.

`
=CONTAR.SI(TOMAR(TOMAR('TOP 3 analysis'!$A:$CY;;4);;-1);"")
`

Son dos TOMAR anidados, y en sus palabras: "un primer TOMAR toma las primeras 4 columnas y el segundo TOMAR se queda con la última columna de estas 4" (el -1 es lo que hace que cuente desde el final). El rodeo parece aparatoso al lado del INDICE, pero lo interesante no es cómo se llega a la columna, sino que al llegar sigue siendo una referencia: TOMAR recorta el rango sin convertirlo en matriz calculada, y por eso las funciones de la familia .SI la aceptan sin protestar. Como remata Leo, "TOMAR también tiene este poder de devolver referencias, y así las funciones punto SI, punto SI punto CONJUNTO, la aceptarán".

Con esto la regla queda cerrada por completo. Para conseguir una referencia hay tres caminos: INDICE omitiendo el argumento de fila, DESREF, o TOMAR. Y si no necesitas comodines, siempre queda cambiar de función y tirar de SUMAPRODUCTO o FILTRAR.

Un fleco que el hilo deja abierto: Joan afirma que hasta hace unas semanas esto le funcionaba, y nadie llega a examinar esa premisa. Si ELEGIRCOLS` nunca ha devuelto una referencia, la fórmula no debería haber funcionado tampoco antes, así que o hubo algún cambio en el libro que no se ha identificado, o el recuerdo no es exacto. Queda sin resolver.

Si alguna vez has escrito una fórmula que te parecía impecable y Excel te ha contestado con un cuadro de diálogo hablándote de apóstrofos, sabes lo desconcertante que es. Le pasó esta semana a un miembro de la comunidad, y el hilo que se abrió a continuación acabó destapando una de las distinciones peor explicadas de Excel: la diferencia entre una referencia y una matriz.

El problema

La fórmula de partida era esta:

=CONTAR.SI.CONJUNTO(ELEGIRCOLS('TOP 3 analysis'!$A:$CY;4);"*")

La idea es sencilla: sacar la cuarta columna de un rango ancho con ELEGIRCOLS y contar en ella las celdas con contenido. Excel, en lugar de calcular o de dar un error informativo, muestra el aviso genérico de "hay un problema con esta fórmula", con su explicación sobre el signo igual y el truco del apóstrofo. Nada de eso guarda relación con la causa, y ese es justamente el motivo de que un fallo así pueda costar una tarde.

La causa

ELEGIRCOLS no devuelve una referencia a celdas: devuelve una matriz calculada, un bloque de valores que existe en memoria y no apunta a ninguna posición de la hoja. El primer argumento de las funciones de la familia .SI (CONTAR.SI, CONTAR.SI.CONJUNTO, SUMAR.SI.CONJUNTO, PROMEDIO.SI) no acepta ese tipo de dato: exige una referencia real.

No es un problema de sintaxis ni de argumentos mal colocados. Es que ese hueco concreto solo admite una clase de dato que ELEGIRCOLS no produce nunca.

Solución por la vía de la referencia

Si quieres seguir usando CONTAR.SI, hay que conseguir una referencia de verdad. INDICE tiene aquí una propiedad muy poco conocida: si omites el argumento de fila, devuelve la columna entera como referencia, no como matriz.

=CONTAR.SI(INDICE('TOP 3 analysis'!$A:$CY;;4);"*")

La misma jugada se puede montar con DESREF, aunque conviene recordar que es una función volátil y recalcula con cada cambio del libro:

=CONTAR.SI(DESREF('TOP 3 analysis'!A1;;3;2^20);"*")

Y hay un tercer camino, menos conocido todavía: TOMAR también conserva la naturaleza de referencia del rango que recorta. Con dos llamadas anidadas se llega a la misma columna sin volatilidad y sin argumentos omitidos:

=CONTAR.SI(TOMAR(TOMAR('TOP 3 analysis'!$A:$CY;;4);;-1);"*")

El primer TOMAR se queda con las cuatro primeras columnas y el segundo, con la última de esas cuatro: el -1 es lo que hace que cuente desde el final. Parece un rodeo al lado de INDICE, pero lo relevante no es cómo se llega a la columna, sino que al llegar sigue siendo una referencia, y por eso las funciones de la familia .SI la aceptan.

Solución por la vía de la matriz

El otro camino es aceptar la matriz y cambiar a una función que sí las digiera. SUMAPRODUCTO es la clásica para esto:

=SUMAPRODUCTO(--(ELEGIRCOLS(J1:M11;3)>""))

La doble negación (--) convierte los VERDADERO y FALSO de la comparación en unos y ceros, y SUMAPRODUCTO los suma. En la misma línea funciona FILAS(FILTRAR(...)), que también trabaja con matrices sin protestar.

Conviene tener presente que los dos caminos no son equivalentes al cien por cien: el primero cuenta usando comodines, y el segundo cuenta celdas cuyo contenido es mayor que texto vacío. Según lo que haya en la columna, el resultado puede diferir.

El detalle del asterisco

Hay una segunda capa en este caso que explica por qué no vale cualquier apaño. El "" del criterio no significa lo mismo según dónde aparezca. En una comparación directa (A1="") es un asterisco literal, un carácter y nada más. Dentro de CONTAR.SI, SUMAR.SI o BUSCARV se convierte en comodín, de modo que "*abc" pasa a significar "cualquier texto que termine en abc".

Como el conteo original se apoya en ese comportamiento, la solución elegida tiene que conservarlo, y por eso la vía de INDICE encaja mejor que reescribir la lógica desde cero.

Funciones clave

  • ELEGIRCOLS: extrae columnas concretas de un rango. Devuelve matriz, no referencia.
  • INDICE: omitiendo el argumento de fila, devuelve una columna entera como referencia. Es la pieza que resuelve el caso.
  • DESREF: construye una referencia a partir de un punto de origen y unos desplazamientos. Volátil.
  • TOMAR: recorta filas o columnas de un rango conservando la referencia. Con índices negativos cuenta desde el final.
  • CONTAR.SI / CONTAR.SI.CONJUNTO: cuentan según criterios, admiten comodines y exigen referencias en el argumento de rango.
  • SUMAPRODUCTO: acepta matrices; con la doble negación cuenta condiciones sin necesidad de referencias.

Conclusión

La regla que sale del hilo cabe en una línea y ahorra mucho tiempo: las funciones de la familia .SI piden referencias; el resto de funciones modernas trabajan con matrices. Cuando una función devuelva una matriz calculada, no la coloques en el hueco del rango de un CONTAR.SI: o consigues una referencia (con INDICE, DESREF o TOMAR), o cambias a una función que acepte matrices.

Casos como este son el día a día de la comunidad de Influexcel: alguien pega una fórmula que no funciona, y en menos de cuatro horas hay tres enfoques distintos sobre la mesa y, sobre todo, una explicación de por qué fallaba. Y a veces, como aquí, el hilo sigue vivo días después de publicarse el caso y aparece un cuarto camino que nadie había mencionado.

Más casos con estas funciones

Más contenido de Excel en InflueXcel