Excel - Función contar.si

Hola a todos,

Tengo un libro con diferentes hojas. Una principal donde cargo los datos del nuevo cliente y mediante macro me traslada los datos a una nueva hoja que automáticamente se renombra con el NIF del cliente.

Lo que quiero hacer es en una nueva hoja resumen que tengo preparada, contar la misma celda (M5) de todas las hojas de clientes que tengo creadas (300 hojas) que cumpla determinado criterio, por ejemplo "SI" o "NO" de la celda en cuestión.

He probado a utilizar :

=contar.si(hoja5:hoja300!M5;"SI") pero me da error ya que estoy utilizando los nombres internos de las hojas y no los que se han ido renombrando con el nif de cada cliente.

Hay alguna forma de meter en la fórmula para utilizar el nombre interno de la hoja?
También quisiera introducir en la formula que en lugar de hoja300 utilice hasta la última del libro para que así cuando se creen más hojas no tenga que ir modificando el número de hoja final.

No sé si tendré que hacerlo mediante VBA o hay algún formato en fórmulas que desconozco para hacer mención al nombre interno de las hojas.

Gracias

Desconozco la razón por la que es necesario crear una hoja por cliente entre otras cosas porque no lo explicas.
Como has podido comprobar es la peor de las estructuras de datos para sacar información.
Para poder este tipo de cálculos debería haber una hoja con todos los datos.

La fórmula seria una cosa así:


o esta:


Pero NO funciona, son 300 hojas y Excel no soporta tantas fórmulas concatenadas.

Soluciones:

Opción 1: (la más correcta) Crear una hoja con los datos de todos los clientes.

Opción 2: Crear una macro

Saludos.
\\//_

Y mediante Macro como sería?

Tengo 1 hoja por cada cliente porque cada cliente necesita tener muchas anotaciones que son homologas pero hay otras muy diferentes, cuadros de texto con comentarios, etc. es decir una hoja muy completa por cada cliente, mis anotaciones personales, etc.

Tengo una hoja principal, desde como he comentado introduzco nuevos clientes y ya me crea la nueva hoja del cliente y también una macro para buscar la hoja de un cliente.

Tienen unos encabezados homólogos para todos, que se ubican en las mismas celdas eso sí (materiales que compran, etc) y ahí es lo que quiero tener un resumen de todos los clientes , es decir por ejemplo : contar los clientes que cumplan que compran un determinado material >1500 o "SI" o "NO"

He intentado poner algo así mediante VBA, pero en lugar de Hoja5.Range("M5") no sé como poner para que tome un rango de hojas desde la hoja5 hasta la última del libro.

Sub RefCodenameHoja()
Hoja3.Range("I3").Formula = _
Application.WorksheetFunction.Countif(Hoja5.Range("M5"), "S")
End Sub

La fórmula para contar seria así:


Se salta la hoja en la que introduces los datos a la que he llamado Principal. El resultado lo muestra en un MsgBox pero lo puede poner en una celda.

También puedes hacer una función personal para usarla como si fuese del sistema:


La llamada en la celda seria asi:


Saludos.
\\//_

Cuando se utiliza Sheets(a)

con (a) se hace referencia a todas las hojas del libro? porque me da error al ejecutar en ese punto, indica subdince fuera.....

Como puedo hacer referencia a todas las hojas existentes del libro ? para así cuando se añadan más hojas no tenga que estar modificando la macro?

con sheets(array( )) entiendo que puedo hacerlo pero no sé como dar la instrucción para indicar todas las hojas del libro sin identificarlas, así me olvido.

No tengo problema ya que la celda M5 únicamente la tengo en las hojas que quiero contar por lo que en las 4 hojas iniciales en esa celda no hay nada por lo que no me importa que la tome también en cuenta.

Gracias

Ya está, ya lo tengo solucionado .

Mil gracias por tu ayuda

Gracias por lo de ayer.

Otra cosa si en la misma macro, quisiera ponerle que hiciera lo mismo con otras celdas de todas las hojas como lo puedo poner?

Es decir tengo que llevar el contaje de 13 celdas de todas las hojas a 13 celdas de la hoja resumen.

Tengo otra celda que me tiene que sumar , como puedo ponerlo ?

Gracias

He probado la que es función en lugar de ir con macros, funciona pero si cambio el valor de alguna celda no lo cambia automáticamente, tengo que ir a la celda de la formula y volver a darle intro.

Cuando pones la función en la celda donde quieres el resultado indicas la referencia de la celda y el valor a buscar, en este caso un string.

Es decir esta función cuenta el número de veces que hay un determinado texto en una determinada celda en todo el libro (todas las hojas)
Para cambiar de celda y de texto solo hace falta cambiar los parámetros:


Con estos parámetros cuenta en cuantas hojas sale la palabra PATATA en la celda A9, no distingue entre mayúsculas y minúsculas.
Para contar celdas con números y para sumar hay que hacer algunos cambios.

Saludos.
\\//_

Hola a todos:
Ya te lo ha dicho Antoni. Esto que haces es un disparate:
Usa una estructura de base de datos. Si son muchos los detalles en varias tablas relacionadas con muchas columnas, las que necesites, para cada detalle, a ser posible agrupadas. Como lo haces es un disparate, difícil de mantener y cada vez te dará más problemas y más complejos.
En Excel, como en la vida, lo primero y más importante es la estructura.
Insisto, no sigas con esto. Si quieres ir de Madrid a Cartagena, antes de llegar a Aranda de Duero, lo que debes hacer es dar la vuelta inmediatamente y cambiar de dirección.
https://www.youtube.com/channel/UCxgRcbeR2q4PpE1Pgft74Iw