Excel - Formulas CONTARA y CONTAR.SI que se modifican internamente

Buenas a todos.
Tengo un problema con unas celdas que tienen un CONTADOR.
Se trata de 2 contadores que están situados en la FILA 7


Lo importante es que debe empezar a realizar su función desde la FILA 8.

¿Y cual es el problema?
Muchas veces tengo que DUPLICAR los datos de la fila 8, para lo que utilizo una macro:

Primeramente selecciono toda la fila 8 y después ejecuto la MACRO

¿Y que me ocurre?
Pues que las fórmulas de contadores se modifican a la fila 9


Si duplico la fila 9 o la 10, no sucede y mantiene las formulas desde la fila 8.

¿Existe alguna manera de corregir esto?
No se si a la fórmula hay alguna manera de decirle algo del tipo 'Cuenta desde la celda de abajo de esta celda (B7 por ejemplo) para que ocurra lo que ocurra, se dupliquen filas o se eliminen, siempre cuente desde la fila 8.

Hola:

Prueba con la función Indirecto..

Algo así:



Comenta si te funciona.

Saludos.

Hola Ignacio.

...pues no estoy muy seguro.
Diría que este: =CONTARA(INDIRECTO("D8:D100")) si que hace su función y aunque duplique la fila 8, no modifica el contador a la fila 9.

Sin embargo, si utilizo ese argumento 'INDIRECTO' en las siguientes fórmulas, no las admite o la fórmula se transforma en ###########

=SUMA(1/CONTAR.SI(INDIRECTO(B8:B100;B8:B100&"")))-1 '<--Esta por ejemplo deja de funcionar.

Lo mismo me ocurre con esta otra que cuenta solo si aparece un valor ubicado en la celda AE5.
=CONTAR.SI(A8:A100;AE5)

Si le indico =CONTAR.SI(INDIRECTO(A8:A100;AE5)) tampoco me funciona :-(

En contar.si le estas diciendo que en el rango de A8:A100 cuente cuantas veces aparece el valor que tienes en AE5. Por supuesto que si en AE5 no hay ningún valor no va a contar nada.

Si usas indirecto el rango debe ir entre comillas.


La otra debería quedar así:


Saludos.

¡¡¡Pues parece que sí funciona, Ignacio!!! GRACIASSSS

Pero si que tengo un problema en la celda que utiliza:

=SUMA(1/CONTAR.SI(INDIRECTO("B8:B100");INDIRECTO("B8:B100"&" ")))-1

Aquí me muestra el clásico error: #¡DIV/0!
Y aunque he entrado al formato de celda y he modificado el 'tipo', no consigo quitar el error.

He probado con tipo ;;; y con 0;;;@ y 0;-0;;@ pero no lo consigo :-(

¿Que debería hacer?

Hola Quico, el error #¡DIV/0! significa que estás dividiendo por 0, no se soluciona cambiando el formato de la celda.... revisa la fórmula CONTAR.SI(INDIRECTO("B8:B100");INDIRECTO("B8:B100"&" ")), si puede dar 0 entonces mete toda la fórmula en un SI.ERROR(), si no puede dar 0 entonces revisa los datos

Salu2
Dirk

Hola Dirk,
pues la verdad es que no consigo arreglar el error #¡DIV/0!

La función que trato de utilizar es para contar el número de valores numéricos únicos que tenemos en un rango. Osea, que si por ejemplo tengo 100,101,101,102 ; el resultado no es 4 números, sino 3 números (se repite 101).


Si utilizo esta fórmula =SUMA(1/CONTAR.SI(B8:B100;B8:B100&""))-1 y le hago Crtl+Mayuscula+Enter y me queda {=SUMA(1/CONTAR.SI(B8:B100;B8:B100&""))-1} me funciona perfectamente.

El problema está en que a veces he de duplicar la fila 8 por completo y entonces la formula modifica el B8 por B9. Eso hace que el contador no cuente correctamente al obviar la celda de la fila 8.

En este post me indicaron que se podía arreglar añadiendo INDIRECTO, para otro tipo de fórmulas. ej: =CONTAR.SI(INDIRECTO("A8:A150");AE5)

¡¡Y la verdad es que sí funciona para la fórmula =CONTAR.SI.

Sin embargo no consigo hacerlo con la que comento.

He probado variantes como:

{=SUMA(1/CONTAR.SI(INDIRECTO("B8:B150");INDIRECTO("B8:B150"&" ")))-1}

{=SI.ERROR(SUMA(1/CONTAR.SI(INDIRECTO("B8:B150");INDIRECTO("B8:B150"&" "))))-1}

{=SUMAPRODUCTO(1/CONTAR.SI(INDIRECTO("B8:B150");INDIRECTO("B8:B150"&" ")))}

...y en todos los casos sigo con el error #¡DIV/0! y sin contar nada!

¿Alguna idea?

Eso es porque en el rango que le estas dando hay alguna o algunas celdas vacias

Estás utilizando mal el SI.ERROR...

Inténtalo con así:


con CTRL+SHIFT+ENTER

Saludos.

Pues has dado en el clavo!!!
Tal cual con:
=SUMA(SI.ERROR(1/CONTAR.SI(INDIRECTO("B8:B150");INDIRECTO("B8:B150"));0))

Desaparece el error y contabiliza el TOTAL de valores únicos en el rango!!
Muchas gracias. :-)

De nada, hasta la próxima...

Hola Quico, que bueno que pudiste solucionar, sin embargo debo reconocer que me "molesta" que el rango de la fórmula esté fijo, quizás tus datos nunca lleguen a la fila 100 o a la 150 pero el hecho que pueda salirse del rango me incomoda así que hice pruebas convirtiendo el rango en una tabla y efectivamente puedes agregar todas las filas que quieras y la fórmula seguirá funcionando.

La famosa fórmula:

pero.... siempre hay un pero.... tu macro DuplicarFila no funcionaría ya que Excel no permite copiar una fila e insertar las celdas copiadas en la tabla, lo hice de forma manual y me dio error, la solución a esto pudiera ser copiar la celda de la fila 8 o de la última fila y pegarla a continuación para que la tabla tome esa celda como parte de tabla pero OJO que es la celda, si copias/pegas la fila como insertar celda, la tabla no ajusta su tamaño, lo que me incomoda sería que los datos quedarían en la última fila.... si aun así quieres que los datos queden en la primera fila entonces mi sugerencia sería insertar una fila a la tabla a nivel de la fila 8 y copiar los datos de la fila 9 y pegar en la fila 8 (puedes ser con tu macro y si, lo puedes hacer con las filas completas), esto sí lo permite y los datos quedarían al principio de la tabla para que sean manipulados de forma mas fácil; este método serviría como solución general para contar valores únicos sin considerar el tamaño del rango incluso si insertas columnas a la izquierda o filas arriba de la tabla.

Nota: mi interés surgió por la falta de conocimiento en implementaciones de las formulas matriciales y por supuesto por una buena dosis de obsesión en soluciones a prueba de usuarios con menos conocimientos de excel..... La verdad que este post me ha servido para reforzas mis conocimientos en fórmulas matriciales.

Salu2 a todos,
Dirk

Hola Dirk,
me alegro que este post haya servido a alguien más.

Muchas gracias por vuestra ayuda.