Contando celdas: la combinación FILAS x COLUMNAS.

Hoy explicaré una forma de contar celdas sin macros, aplicado a un problema concreto planteado por un lector:

Requiero hacer una macro que busque un nombre (Hoja 1)en la columna A por ejemplo (Carlos) y que al mismo tiempo en la columna B tome el valor que tiene al lado, es decir: Columna A Columna B Carlos 8 Roberto 10 Carlos 4 y que sume el valor del numero las veces que se repita el nombre de Carlos. Lo interesante es que la suma del valor se distribuya en unos como una matriz en una Hoja 2 en un rango de 5 filas a lo largo de todas las columnas es decir si tengo una suma de 12 Columna A B C D E F 1 1 1 1 2 1 1 1 3 1 1 4 1 1 5 1 1

Veremos la manera de emplear las funciones estándar de hoja de cálculo a nuestro alcance para conseguir nuestro objetivo (distribuir una cantidad por los elementos de una matriz) sin necesidad de implementar una macro.
Para ello veamos de qué origen de datos partimos:

Hemos insertado en la celda D3 la función:
=SUMAR.SI(A1:A11;D2;B1:B11)
con el fin de tener el primer resultado solicitado, la suma de los valores de la columna B que coinciden con el valor 'carlos'.

El siguiente paso para conseguir nuestra matriz de cinco filas con 'unos' distribuidos será trabajar una 'matriz auxiliar'. Asi que no situaremos en la celda H2 y escribiremos:
=FILAS(H$2:H2)*COLUMNAS(H$2:H2)+MAX(G$2:G$6)
también podríamos haber optado por esta algo más sencilla:
=FILAS(H$2:H2)+MAX(G$2:G$6)
luego copiaremos cinco filas hacia abajo y tantas columnas a la derecha como necesitemos... esto resultará una numeración de arriba hacia abajo y de izquierda hacia derecha consecutiva:

Fijémonos en que no haya datos en la columna G.
El funcionamiento de esa operación =FILAS(H$2:H2)*COLUMNAS(H$2:H2) devuelve una numeración de filas por cada columna (1,2,3,4 y 5 para nuestro ejemplo); y a continuación sumamos el valor máximo de la columna anterior con +MAX(G$2:G$6); asegurándonos entonces ese secuencia correlativa con saltos de 5 en 5 por cada columna.

El siguiente y último paso consite en emplear nuestros valores calculados en la matriz auxilar y aplicarlos con el fin de obtener la distribución pedida.
Como ya conocemos cuál es el valor a distribuir, en la celda D3, bastará aplicar un condicional del tipo y escribir en H9:
=SI(H2<=$D$3;1;"")
arrastrando al resto de elementos de la matriz final:

Así en dos pasos tenemos lo que se solicitaba, y sin macros.

En el ejemplo he expuesto dos maneras de conseguir el mismo resultado, la primera consistía en multiplicar la función FILAS por la función COLUMNAS (aunque realmente para el ejemplo no era necesario); pero me parece interesante recordar cómo podemos contar celdas de un rango o matriz con funciones de Excel; y es que sería tan sencillo como calcular el producto del número total de filas por el número de columnas de dicha matriz:
=FILAS(H2:M6)*COLUMNAS(H2:M6)
por ejemplo, la matriz H2:M6 tiene 30 celdas (5 filas x 6 columnas).