Formula "custom" parecida a vlookup

No necesitas recurrir a VBA, pero la fórmula es algo complicada. Por ejemplo, para sumar las columnas B:F de la fila en cuya columna A (entre las filas 1 a 50) se encuentre "valor", la fórmula sería:

=SUMA(INDIRECTO("F"&COINCIDIR("valor";A1:A50;0)&"C"&COINCIDIR("valor";A1:A50;0)&":F"&COINCIDIR("valor";A1:A50;0)&"C"&6;0);FALSO)

El 6 que hay al final de la fórmula es lo que determina el número de columnas hacia la derecha que se sumarán.

Quizás tengas que sustituir los puntos y coma de la fórmula por comas, dependiendo de la configuración de tu equipo.
Un saludo


Fernando Arroyo
MS MVP - Excel

"Gustavo" escribió en el mensaje news:

Tengo que armar una formula parecida al vlookup, pero que en lugar de traerme
el dato que este a x columnas de distancia de la referencia buscada, me
permita sumar los nros que esten entre 2 columnas ubicadas a la derecha del
dato.
la formula deberia ser mas o menos asi:
vloolsumar(referencia,tabla,columna1,columna2,range_lookup)
columna1 deberia ser el nro de columnas donde arranca la suma, y columna2 el
nro de columnas donde finaliza la suma)
El tema es que no tengo claro como armar el codigo vba para desarrollarlo.
Alguien me ayuda??
gracias!!!!

hola, chicos !

Gustavo escribio en el mensaje
... armar una formula parecida al vlookup ... que ... permita sumar ... entre 2 columnas ... a la derecha del dato [...]

Fernando Arroyo escribio en el mensaje
... formula ... para sumar las columnas B:F de la fila en cuya columna A (entre las filas 1 a 50) se encuentre "valor" [...]

[solo una observacion]...
-> la formula propuesta por Fernando, 'construye un rango' usando la notacion 'FC' [#_de_Fila y #_de_Columna]
[ese es el 'objetivo' de las letras F y C que la formula 'usa' entre comillas dobles]
dicha notacion pudiera ser diferente y necesitaria ser 'cambiada' a 'LC' [L_de_Linea 'en lugar de' F_de_Fila]
[dependiendo de la configuracion regional del equipo y la version de excel -si es en español-]

-> otra funcion 'alterna' [incluso mas 'breve'] seria 'construir el rango' usando desref [offset si es excel en ingles]
si suponemos que el rango de la 'base de datos' es 'A1:M26' [en donde...]
la columna A son los nombres_productos y la fila 1 contiene los nombres_meses [datos 'reales' seria 'B2:M26']
y en 'otra' parte de la hoja se definen los parametros para buscar y sumar [p.e.]
[B30] => el producto que se busca
[B31] => el mes 'inicial' para sumar un acumulado
[B32] => el mes 'tope' para acumular las ventas
[B33] => una formula para sumar las ventas 'dentro' del periodo 'señalado' [desde_hasta]
=suma(desref(a1,coincidir(b30,a2:a26,0),b31,1,b32-b31+1))
=sum(offset(a1,match(b30,a2:a26,0),b31,1,b32-b31+1)) [si se trata de excel en ingles]

saludos,
hector.

Gracias a ambos, pero no me esta funcionando. Me parece que es porque la
referencia esta en otro workbook. Puede ser?

"Héctor Miguel" wrote:

hola, chicos !

> Gustavo escribio en el mensaje
> ... armar una formula parecida al vlookup ... que ... permita sumar ... entre 2 columnas ... a la derecha del dato [...]

>> Fernando Arroyo escribio en el mensaje
>> ... formula ... para sumar las columnas B:F de la fila en cuya columna A (entre las filas 1 a 50) se encuentre "valor" [...]

[solo una observacion]...
-> la formula propuesta por Fernando, 'construye un rango' usando la notacion 'FC' [#_de_Fila y #_de_Columna]
[ese es el 'objetivo' de las letras F y C que la formula 'usa' entre comillas dobles]
dicha notacion pudiera ser diferente y necesitaria ser 'cambiada' a 'LC' [L_de_Linea 'en lugar de' F_de_Fila]
[dependiendo de la configuracion regional del equipo y la version de excel -si es en español-]

-> otra funcion 'alterna' [incluso mas 'breve'] seria 'construir el rango' usando desref [offset si es excel en ingles]
si suponemos que el rango de la 'base de datos' es 'A1:M26' [en donde...]
la columna A son los nombres_productos y la fila 1 contiene los nombres_meses [datos 'reales' seria 'B2:M26']
y en 'otra' parte de la hoja se definen los parametros para buscar y sumar [p.e.]
[B30] => el producto que se busca
[B31] => el mes 'inicial' para sumar un acumulado
[B32] => el mes 'tope' para acumular las ventas
[B33] => una formula para sumar las ventas 'dentro' del periodo 'señalado' [desde_hasta]
=suma(desref(a1,coincidir(b30,a2:a26,0),b31,1,b32-b31+1))
=sum(offset(a1,match(b30,a2:a26,0),b31,1,b32-b31+1)) [si se trata de excel en ingles]

saludos,
hector.

Al tratarse de otro libro, tienes que incluir tanto su nombre como el de la hoja en la fórmula. Suponiendo que el libro fuera Libro1 y la hoja Hoja1, la fórmula sería:

=SUMA(INDIRECTO("[Libro1.xls]Hoja1!F"&COINCIDIR("valor";'C:\[libro1.xls]Hoja1'!A1:A50;0)&"C"&COINCIDIR("valor";'C:\[libro1.xls]Hoja1'!A1:A50;0)&":F"&COINCIDIR("valor";'C:\[libro1.xls]Hoja1'!A1:A50;0)&"C"&6;0);FALSO)

Ten en cuenta que la fórmula no funcionará si el libro está cerrado, y que si el nombre del libro y/o el de la hoja tuvieran un espacio, tendrías que encerrarlos entre comillas simples, p.ej.:

'[Libro 1.xls]Hoja 1'

Un saludo.


Fernando Arroyo
MS MVP - Excel



"Gustavo" escribió en el mensaje news:

Gracias a ambos, pero no me esta funcionando. Me parece que es porque la
referencia esta en otro workbook. Puede ser?

"Fernando Arroyo" wrote:

"> Ten en cuenta que la fórmula no funcionará si el libro está cerrado,"
Por esto es que necesito una funcion personalizada. El archivo que trae los
datos debo compartirlo (via mail) con otras personas. Y este tipo de datos
los traigo de varios archivos los cuales no deseo compartir con los otros
usuarios. Actualmente lo que hago es duplicar toda la informacion (manejando
tablas mensuales y acumuladas), pero la idea es achicar el manejo de "bases
de datos" y que los reportes funcionen mas sencillamente
Gracias de nuevo
Gustavo