lunes, 1 de febrero de 2010

Sumas condicionadas: SUMAR.SI.CONJUNTO.

Días atrás expliqué brevemente cómo trabajar con la función CONTAR.SI.CONJUNTO en Excel 2007; hoy abordaremos una función similar SUMAR.SI.CONJUNTO.
Escribía un lector a mi correo:
...Cuando recibo una reclamación tengo que contabilizarla según el motivo y el grupo de trabajo al que pertenece el operador causante, y dependiendo del canal por el que la reciba la cuento en una hoja o en otra.
{=SUMA(SI(motivo="OPERATIVA";SI((recibida="INSAT M001");SI(oper="2471346";1;0))))}
Con esta suma condicional matricial consigo que si el motivo (columna H) es exactamente igual a “OPERATIVA” y la he recibido (columna F) por el documento “INSAT M001” y el operador causante (columna H) es el “2471346” me lo sume/cuente correctamente.
El problema me surge si intento utilizar * o´ ?? para los campos motivo o recibida (por ejemplo: “INSAT*”, para contar todas las que empiecen así. Además para el campo oper necesitaría que me contara todos los que pertenecen a cada grupo (están agrupados por centenas; por ejemplo el grupo L son del 2471301 al 2471600...

Dispondremos de la siguiente tabla de datos donde aplicaremos la solución empleando la función
=SUMAR.SI.CONJUNTO(rango_suma; rango criterio1; criterio1; rango criterio2; criterio2; etc)
El ejercicio consite en usar una función de SUMA para simular la función ya vista de CONTAR.

Sumas condicionadas: SUMAR.SI.CONJUNTO.


Ya que vamos a emplear la función SUMAR.SI.CONJUNTO incluiremos en el rango de celdas $D$2:$D$11 el valor 1, para que sume uno cada vez que cumpla los criterios que le asignaremos. También por comodidad hemos creado los siguientes nombres:
motivo =Ejem!$A$2:$A$11
oper =Ejem!$B$2:$B$11
recibida=Ejem!$C$2:$C$11

Empezaremos por unas condiciones sencillas:

Sumas condicionadas: SUMAR.SI.CONJUNTO.



Conocemos ya la forma matricial para una suma condicional matricial, que si aplicaramos en este ejercicio sería:
{=SUMA(SI(motivo=$G$2;SI((recibida=$G$4);SI(oper=$G$3;1;0))))}
o también
{=SUMA(SI(motivo="OPERATIVA";SI((recibida="INSAT M001");SI(oper=2471346;1;0))))}
es decir, cuando el rango 'motivo' coincida con el valor de la celda G2, y además el rango 'recibida' con la celda G4 y el rango 'oper' con G3 entonces suma 1; el resultado en este caso para las condiciones dadas es que hay dos registros que cumplen esos criterios.

Si nos hubieramos decantado, al trabajar con Excel 2007, por la función SUMAR.SI.CONJUNTO, hubieras escrito:
=SUMAR.SI.CONJUNTO($D$2:$D$11;motivo;$G$2;oper;$G$3;recibida;$G$4)
esto es, suma los valores del rango $D$2:$D$11 cada vez que un registro cumpla que el rango 'motivo' coincida con el valor de la celda G2, el rango 'recibida' con la celda G4 y el rango 'oper' con G3.

Una vez controlada esta función, podemos complicarlo un poco para adaptarlo a la consulta del lector. ¿Cómo consigo, utilizando los comodines de Excel * y ?, forzar ciertas condiciones?; en concreto:

Sumas condicionadas: SUMAR.SI.CONJUNTO.


Si ejecutamos una suma condicional en forma matricial tendríamos:
=SUMA(SI(motivo="Operativa";SI(IZQUIERDA(recibida;5)="INSAT";SI(oper>=2471301;SI(oper<=2471600;1;0)))))
donde se hace imposible incorporar esos comodines deseados, por lo que han sido sustituidos por una función de texto
IZQUIERDA(recibida;5)="INSAT"
que replica, de manera similar, el uso del comodín *; es como si dijeramos todos los elementos del rango 'recibida' que comiencen por 'INSAT'. Los demás criterios no necesitan aclaración, ¿verdad?.
En Excel 2007 surge la función SUMAR.SI.CONJUNTO, con alguna ventaja añadida a esta forma matricial anterior; y es que podremos incorporar a esos criterios los comodines estándar, veámoslo:
=SUMAR.SI.CONJUNTO($D$2:$D$11;motivo;"Operativa";oper;">=2471346";oper;"<=2471600";recibida;"INSAT*")

obviamente, también podríamos haber empleado la función
=CONTAR.SI.CONJUNTO(motivo;"Operativa";oper;">=2471346";oper;"<=2471600";recibida;"INSAT*") con similar estructura, y misma definición de criterios.

donde el comienza por INSAT se determina de la forma 'INSAT*'.

En todos los casos, el resultado es idéntico, existen cuatro registros que cumplen las condiciones dadas.

88 comentarios:

  1. Tengo un problema con excel. Al sumar dos celdas, por ejemplo128,35 +10,27 debería darme138,62 y me da 138,61€. ¿Cómo puede pasar eso? Gracias

    ResponderEliminar
  2. Hola,
    lo que ocurre es que tienes un formato de celda que te muestra sólo dos decimales (lo que no significa que el valor calculado tenga más).
    En tu ejemplo, probablemente la base imponible suma 128,345, y al tener un formato de dos decimales te lo muestra como 128.345; al calcular el IVA (8%) sobre la base imponible lo hace sobre 128.345, lo que devuelve un valor de 10.2676 (y muestra como 10.27); al sumar los dos valores obtienes 128.345+10.2676 = 138.6126 (muestra 138.61), cuando aparentemente estás sumando 128.35+10.27 = 138.62.
    Para verlo mejor da formato a tus celdas y que muestren 4 decimales...

    ResponderEliminar
  3. hola tengo un problema y excel me suma mal si voy a sumar 560.50 + 46.69 el resultado de que arroja excel es 607.18 y en realidad es 607.19 como soluciono este error gracias

    ResponderEliminar
  4. Hola, revisa el comentario anterior... es más que probable que te ocurra la misma situación.
    Seguramente sea un tema de formato, tu visualizas 560.50 pero el valor podría ser 560.495 y ves 46.69 cuando el valor es 46.685
    Compruebalo y me comentas
    Slds

    ResponderEliminar
  5. Estoy teniendo un problema: No logro que funcione si la condición es que sea una celda no vacía.
    Por ejemplo, tengo esto:
    =SUMAR.SI.CONJUNTO($V$:$V8;$S2:$S8;$S8;$B2:$B8;"Z01";$D2:$D8;"3"), ¿Pero cómo hago si quiero que en vez de "Z01" sea simplemante "No vacío" (cualquier valor distinto de null)?

    ResponderEliminar
  6. Hola Fer Cipriani,
    para sumar condicionado a los NO VACÍOS, podrías utilizar el truco de "=*", en tu caso:
    =SUMAR.SI.CONJUNTO($V$:$V8;$S2:$S8;$S8;$B2:$B8;"=*";$D2:$D8;"3").
    Un saludo

    ResponderEliminar
    Respuestas
    1. Daniel Bibliaenero 15, 2012

      Esto funciona siempre y cuando la celda no tenga ningún contenido, ni siquiera una fórmula.
      Por ejemplo, si en la columna B tuviera en alguna celda la fórmula ="" (vacío) que genera una celda sin contenido, sin embargo si es considerado en la suma. Habría que usar ="?*", así no la cuenta.

      Eliminar
    2. Buena puntualización...
      gracias Daniel Biblia.
      Slds

      Eliminar
    3. Pues parece que no funciona. Yo tengo exactamente el mismo problema, ¿es posible que vuestras respuestas estén mal puntuadas? ¿que tengan las comillas en el lugar equivocado? Necesito ayuda, por favor!

      Eliminar
    4. Hola Miguel,
      dime cómo tienes tu escrita tu función, por que las funciones puestas por mi están todas probadas. Aunque es cierto que en ocasiones, dependiendo de la configuración de Microsoft, haya que cambiar puntos y coma por puntos.
      Slds

      Eliminar
  7. Hola, quisiera saber si me pudieras ayudar con un problema que se me ha presentado con para usar un comodin en una macro, el problema es el siguiente:
    Workbooks.Open ThisWorkbook.Path & "\Sistema Estadístico ver " & * & ".xls

    he tratado de abrir el fichero Sistema Estadistico ver 1.0.12 pero me da error, necesito el comodin porque el numero de la version ira cambiando, mientras lo demas permanecera fijo, he probado de muchas formas pero siempre me da error, me podrias ayudar por favor.Gracias de antemano.

    ResponderEliminar
  8. COMO PUEDO ASIGNAR UN VALOR DE UNA CELDA EN ESTA FORMULA, YA QUE SOLO ME TOMA LA SECUENCIA DE TEXTO Y YO QUIERO QUE ME TOME EL VALOR DE LA CELDA
    =SUMAR.SI.CONJUNTO($F$2:$F$900000,$C$2:$C$900000,H3,$E$2:$E$900000,">=01/01/2012",$E$2:$E$900000,"<=20/02/2012")
    EN LA CONDICION DE LA FECHA YO QUIERO QUE SEA F2 POR EJEMPLO Y ME HAGA LA OPERACION DE COMPARAR LA FECHA CON RESPECTO AL VALOR DE LA CELDA

    ResponderEliminar
    Respuestas
    1. Hola Chavaseca,
      deberías reemplazar el argumento de criterio por:
      "<="&F2
      quedándote algo así tu fórmula:
      =SUMAR.SI.CONJUNTO($F$2:$F$900000,$C$2:$C$900000,H3,$E$2:$E$900000,">="&F1,$E$2:$E$900000,"<="&F2)
      Un saludo

      Eliminar
  9. no aparecen las formulas contar.si.conjunto ni sumar.si.conjunto porque?

    ResponderEliminar
    Respuestas
    1. Hola,
      estas funciones no existían en versiones 2003 y anteriores, nacieron con Excel 2007 y se mantienen en Excel 2010. ¿Qué versión es la tuya?
      Slds

      Eliminar
  10. Buenas tardes

    La función sumar.si.conjunto es compatible con las celdas combinadas? Es que tengo que hacer una búsqueda de entre dos columnas, la primera de ellas tiene columnas combinadas y la segunda no. Es decir la posición b24 engloba las posiciones c24,c25,c26 por ejemplo.

    Muchas gracias por su ayuda

    ResponderEliminar
    Respuestas
    1. Hola Joe,
      en principio cuando usamos celdas combinadas, esa celda final única coge como referencia la superior izquierda de la agrupación; asi que cualquier fórmula o función que empleemos sobre éstas, sólo reconocerá dicha celda, y cualquier relación o correspondencia de rangos con su celda homónima.
      En tu caso B24 sólo reconocerá la C24... es el problema de trabajar con Celdas combinadas (que no recomiendo).
      Un cordial saludo

      Eliminar
  11. Estimado, le pregunto para el caso de un excel 2003, que no posee dicha función; el mismo ejercicio lo hago con suma matricial (presionando Crtl+Shift+Enter), pero a la hora de incluir fechas y celdas vacias como condiciones me da error: #¡NUM!. Como puedo hacer una formula con condicones de fechas (entre intervalos de tiempo, ej, entre el 15/05 y el 20/05) y con celdas vacias en Excel 2003. Muchas gracias. Saludos.

    ResponderEliminar
    Respuestas
    1. Hola,
      bueno, a la hora de trabajar con fechas hay que tener mucho cuidado, ya que suelen fallar bastante. Lo mejor par evitar los errores es poner las fechas en forma de función (FECHA(año; mes; dia)); por ejemplo, matricialmente (presionando Crtl+Shift+Enter):
      {=SUMA(SI(rng_fecha>FECHA(2012;5;15);SI(rng_fecha<FECHA(2012;5;20);1;0))}
      para vacías, la condición sería
      {=SI(rng_celdas="";1;0)}
      o bien incluirlo como condición de la anterior.
      Un saludo

      Eliminar
  12. Cuando uso tablas (ctrl+T) no puedo usar como criterio "*" (que significa que tenga en cuenta todos los datos involucrados de ese criterio) para ese caso cual es la sintazxis?

    ResponderEliminar
    Respuestas
    1. Hola Yuri Ventura,
      no sé en que versión de Excel trabajas, pero he probado con 2007 y 2010, y si aplicas el criterio de filtro personalizado en una tabla, es igual a * funciona y muestra, logicamente todos los registros de la tabla.
      Otra cosa distinta es la necesidad de aplicar un filtro para extraer los elementos que ya estás viendo, es decir, toda la tabla...
      Slds

      Eliminar
    2. Bueno, la versión en la que trabajo es excel 2007; como comentaba mi problema empieza cuando a la data que tengo le inserto "Tabla" porque cuando no uso esta opción el criterio "*" funciona dándome la suma correcta, mientras que para el mismo caso usando "Tabla" la suma me arroja un 0.

      Eliminar
    3. Hola Yuri,
      no me queda claro con tu respuesta si quieres aplicar un ciriterio de filtro en la Tabla, o es que estás intentando formular alguna función de SUMA condicionada...
      Quizá sea más sencillo si me envías un ejemplo, con una explicación, a:
      excelforo@gmail.com

      Slds

      Eliminar
    4. Hola de nuevo, bueno creo que acabo de dar solución a mi problema, sucede que este criterio "*" no funciona cuando se trata de números (ejm: tengo una base de datos con campos como: sucursal, cód de producto, volumen de venta, trimestre; la idea era sumar los datos de la sucursal "x" con código "y" del trimestre "z") porque cuando seleccionaba el rango trimestre que tenia los datos (1,2,3 ó 4) e ingresaba el criterio "*" arrojaba 0 como resultado de la suma, así que reemplacé el "*" por ">0" y ahora si arroja un resultado válido...imaginé que este criterio "*" serviría porq la sintaxis es parecida a la del sql cuando quiero extraer datos, eso era todo.
      Muchas gracias por la atención.
      Saludos

      Eliminar
    5. Hola Yuri,
      es decir, finalmente estás aplicando una función SUMAR.SI.CONJUNTO. Ten cuidado con el filtro que comentas, ya que si pones '>0' sólo sumarás los positivos que cumplan las otras dos condiciones...
      Slds

      Eliminar
    6. Hola, claro, en este caso no habría ningún problema pues lo registros en este campo no pueden ser negativos, muchas gracias por la observación. Saludos

      Eliminar
  13. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  14. hola, como puedo hacer para tomar como condicionante por relleno en una celda o la falta del mismo.

    saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      no sé cuál es el fin de tu pregunta (quizá aplicar algún criterio de filtro?) o a que te refeires con 'condicionante por relleno'.
      Voy a entender que quieres evaluar si una celda tiene algún relleno de fondo de celda, y quieres en otra celda a parte evaluar con una función Condicional SI si es así o no.
      Para esto deberás trabajar con macrofunciones, es decir crear un nombre definido (voy a llamarlo 'relleno') con referencia:
      =INDICAR.CELDA(63;Hoja1!$A1)+AHORA()*0
      a continuación, en la celda B1, verificamos si la celda A1 tiene relleno o no de fondo, por lo que introducimos la siguiente fórmula:
      =SI(relleno;"fondo";"blanco")

      Espero haber adivinado tu cuestión.
      Slds

      Eliminar
  15. Buenas...
    Tengo una tabla con 17 columnas. La primera fila contiene la cabezera de lo que es cada columna ( MATERIAL//N//V//R//I//N//V//R//I//N//V//R//I//N//V//R//I// ) como os he puesto. La columna MATERIAL, está cumplimentada con materiales no repetidos ( 100 materiales diferentes ) y el resto de las columnas van rellenadas aleatoriamente con números enteros.
    Mi pregunta es la siguiente, quiero saber como puedo sumar los valores de cada material, diferenciandolo por el tipo de columna, sin tener que ir sumando uno a uno; por ejemplo:

    MATERIAL: N V R I N V R I N V R I N V R I
    TORNILLO 1 0 2 0 0 5 3 0 0 0 0 0 1 2 0 0
    TUERCA 0 0 0 0 1 3 5 0 0 0 0 1 4 0 0 0

    TORNILLO: TOTAL-N=2; TOTAL-V=7; TOTAL-R=5; TOTAL-I=0
    TUERCA: TOTAL-N=5; TOTAL-V=3; TOTAL-R=5; TOTAL-I=1

    Un saludo y gracias de antemano.

    ResponderEliminar
    Respuestas
    1. Hola!
      supongamos que tus datos están en el rango A1:Q3, en la primera fila A1:Q1 la cabecera con los rótulos.
      Entonces, por ejemplo, en R2 insertamos el total para N con la fórmula:
      =SUMAR.SI(A1:Q1;"N";A2:Q2)
      en S2
      =SUMAR.SI(A1:Q1;"V";A2:Q2)
      en T2
      =SUMAR.SI(A1:Q1;"R";A2:Q2)
      en V2
      =SUMAR.SI(A1:Q1;"I";A2:Q2)
      con lo que tendríamos los totales para TORNILLO

      Arrastrar (con cuidado de fijar adecuadamente los rangos) y obtendríamos lo mismo para el resto de materiales.

      Espero te sirva.
      Saludos

      Eliminar
    2. Muchas gracias... Así lo hice y me funcionó...

      Ahora tengo otra consulta... Tengo en una hoja la plantilla de un mes con 31 día ( en horizontal de B hasta BDI). En la columna A se encuentran los nombres de MATERIAL y los días estan compuestos por 4 columnas ( al igual que N, V, R, I ).

      En otra hoja, tengo los registros de cada MATERIAL de todo el año, y me gustaria que indicando en la plantilla tipo mes, el mes a consultar, me mostrara esos valores que existen en la hoja de registros. Sé que podría hacerse con una tabla dinámica, pero me gustaría evitarla, pues ha de usar esta hoja una persona no muy puesta en EXCEL.

      Un saludo.

      Eliminar
    3. Hola de nuevo,
      bueno, pues si no quieres emplear tablas dinámicas y tampoco está muy puesta en tablas dinámicas, podrías emplear la función SUMAR.SI.CONJUNTO sobre la hoja de todo el año, aplicando una condición sobre la fecha a mostrar, entendiendo que la hoja donde están los datos tiene un campo de Fecha o con una descripción del mes...
      Habría que ver esa distribución de datos para concretar algo más.
      Slds

      Eliminar
    4. Buenas...

      La distribución sería algo así...

      A B C D E F G H I J K L M N O
      1 MES? 01-01-2013 02-01-2013 03-01-2013 .....
      2 N V R I N V R I N V R I N ...
      3 TORNILLO 1 0 2 0 0 5 3 0 0 0 0 0 1 ...
      4 TUERCAS 0 0 0 0 1 3 5 0 0 0 0 1 4 ...


      La formula tendría que ser ( dicho en palabras )...

      - El VALOR que tenia el TIPO N tal FECHA en este MATERIAL es...


      No sé si me explico. Para cuando variara la celda MES, buncara esos valores en otra hoja con "similares" caracteristicas...

      Espero que te sirva de orientación. Un saludo.

      Eliminar
    5. Hola...
      mejor envíame un ejemplo a
      excelforo@gmail.com
      Slds

      Eliminar
  16. Buenas...
    Ya lo he resuelto...
    Ésta es la formula que he aplicado... LARGA, pero eficaz.


    =SI.ERROR(SI(INDICE(TIPO;1;COINCIDIR(D$4;FECHA_BUS;0))=D$5;INDICE(VALOR_DATOS;COINCIDIR($C6;N_CONDUCTOR;0);COINCIDIR(D$4;FECHA_BUS;0));SI(INDICE(TIPO;1;COINCIDIR(D$4;FECHA_BUS;0)+1)=D$5;INDICE(VALOR_DATOS;COINCIDIR($C6;N_CONDUCTOR;0);COINCIDIR(D$4;FECHA_BUS;0)+1);SI(INDICE(TIPO;1;COINCIDIR(D$4;FECHA_BUS;0)+2)=D$5;INDICE(VALOR_DATOS;COINCIDIR($C6;N_CONDUCTOR;0);COINCIDIR(D$4;FECHA_BUS;0)+2);SI(INDICE(TIPO;1;COINCIDIR(D$4;FECHA_BUS;0)+3)=D$5;INDICE(VALOR_DATOS;COINCIDIR($C6;N_CONDUCTOR;0);COINCIDIR(D$4;FECHA_BUS;0)+3);SI(INDICE(VALOR_DATOS;COINCIDIR($C6;N_CONDUCTOR;0);COINCIDIR(D$4;FECHA_BUS;0)+3)="";0;"")))));"0")


    Un saludo y gracias por tu ayuda.

    ResponderEliminar
  17. N_CONDUCTOR es la MATRIZ de materiales.
    VALOR_DATOS es la MATRIZ de datos adquiridos.
    FECHA_BUS es el valor del mes a buscar en los datos adquiridos.

    ResponderEliminar
    Respuestas
    1. UFFF...
      pedazo de fórmula, quizá buscando se pueda encontrar una manera más eficiente, pero como siempre digo si te funciona bien está.
      Slds

      Eliminar
  18. Buenas...estimado, vuelvo con una consulta sobre una el uso de fórmulas.
    Verás yo tengo una base de datos, le inserte una tabla para que mis fórmulas se actualicen solas (fórmulas como sumar.si o sumar.si.conjunto, etc.) pues estas apuntan a un campo en específico (aunque de eso debes estar más enterado que yo jeje) bueno el problema es el siguiente cuando yo quiero fijar una celda uso el signo "$" pero qué pasa cuando quiero fijar un campo de una tabla predefinida por excel; cuando creo una tabla y quiero invocar esta tabla tiene esta estructura Nombre_Tabla[Nombre_Campo], y yo lo que busco es fijar este campo en mi fórmula y aún no tengo idea de cómo hacerlo a ver si das una manito con este problema...te estaré muy muy agradecido.

    ResponderEliminar
    Respuestas
    1. Hola Yuri,
      bueno, cuando en una función/fórmula haces referencia al campo de una tabla 'Nombre_Tabla[Nombre_Campo]', no hace falta fijar con dólares nada, ya que siempre (muevas o arrastres la fórmula), ésta apuntará a ese rango (a ese campo de la tabla).
      Esta es una de las ventajas de trabajar con Tablas, en esencia es como si trabajaramos con un Nombre definido sobre el rango que ocupa ese campo.

      Verifica que no se te mueve o desplaza el campo al arrastrar la fórmula... y me cuentas.
      Slds

      Eliminar
    2. Hola Ismael...claro en principio pensé que era así pero el problema es que sí se me desplaza cuando arrastro la fórmula a la derecha o a la izquierda, es decir, si arrastro una celda hacia la derecha la fórmula salta a la siguiente columna de la derecha de la tabla y por eso quisiera fijar la columna para cuando quiera arrastar a la derecha o a la izquierda.

      Saludos.

      Eliminar
    3. Hola Yuri,
      ¿puedes enviarme el fichero a excelforo@gmail.com?
      Tendría que verlo, ya que he comprobado la operativa, y a mí no se me desplaza...
      Slds

      Eliminar
    4. Hola Ismael,
      acabo de enviarte un extracto de la base de datos....espero puedas ayudarme, muchas gracias.

      Saludos.

      Eliminar
  19. Buen día requiero obtener el valor máximo de una columna basado en un criterio de otra u otras. Algo así como un max.si.conjunto seria el nombre de la función si Microsoft me dijera que la nombrara.
    No puedo filtrar ni ordenar previamente los datos lo cual solucionaría obviamente mi inconveniente; debo realizar la formula para ello. Con que función o combinación de funciones puedo hacer esta operación.
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      bien, una equivalente sería la fórmula matricial siguiente:
      =MAX(SI(B1:B10="aa";SI(C1:C10="xxx";A1:A10)))
      Recuerda ejecutarla presionando Ctrl+Mayusc+Enter en lugar de sólo Enter.
      En A1:A10 estarían los valores de donde obtener el máximo, en B1:B10 y C1:C10 las columnas donde aplicar los criterios, que en el ejemplo es que sean "aa" y "xxx".

      Espero te sirva.
      Slds

      Eliminar
  20. Buenas,

    ¿Hay alguna manerda de utilizar el sumar.si.conjunto para sumar varias columnas?

    Si no es así, ¿existe alguna formula alternativa que lo haga sin tener que recurrir a una suma de sumar.si.conjunto?

    Es decir:

    Necesito sumar el contenido de las columnas B y C segun si se cumple el criterio X en la columna A.
    Una opción seria la que he comentado de sumar.si.conjunto(B:B;A:A;X)+
    sumar.si.conjunto(C:C;A:A;X), pero preferiría una del estilo sumar.si.conjunto(B:C;A:A;X).

    ResponderEliminar
    Respuestas
    1. Hola!!
      podrías optar por una matricial, del estilo:
      =SUMA(SI(A1:A9="x";C1:C9;0);SI(A1:A9="x";B1:B9;0))

      pero en esencia sería una versión parecida a la que planteas con dos SUMAR.SI.CONJUNTO.

      Espero te pueda servir.
      Slds

      Eliminar
  21. Buenas tardes, necesito hacer que la funcion "sumar si conjunto", me acepte en un criterio la funcion "o", de manera que tome uno de dos valores que se presente

    ResponderEliminar
  22. la tengo formulada asi:
    SUMAR.SI.CONJUNTO(Hoja1!$L:$L,Hoja1!$M:$M,A36,Hoja1!$D:$D,"*"&501,Hoja1!$V:$V,"*HR*")
    pero necesito que donde dice ,"*"&501 me acepte que sea 501 ó 502

    gracias de antemano por su ayuda

    ResponderEliminar
    Respuestas
    1. Hola!!
      lo malo de la función SUMAR.SI.CONJUNTO es que sólo admite por sí sola la condción Y, si quieres que funciones como O tendrías que sumar dos funciones SUMAR.SI.CONJUNTO, una para 501 mas otra para 502:
      =SUMAR.SI.CONJUNTO(Hoja1!$L:$L,Hoja1!$M:$M,A36,Hoja1!$D:$D,"*"&501,Hoja1!$V:$V,"*HR*")+SUMAR.SI.CONJUNTO(Hoja1!$L:$L,Hoja1!$M:$M,A36,Hoja1!$D:$D,"*"&502,Hoja1!$V:$V,"*HR*")

      Otra opción sería trabajar con la función SUMAPRODUCTO, echa un vistazo a esta explicación:
      http://excelforo.blogspot.com.es/2013/02/las-posibilidades-de-sumaprodcuto-en.html

      Espero te sirva.
      Slds

      Eliminar
  23. Hola
    tengo una pregunta como haria para que mediante una macro excel me trajera la informacion de otro libro por ejm tengo el libro 1 y quiero que cuando lo habra o mediante un boton me actualice los datos dados en el libro 2 que seria como una especie de base de datos
    De antemano muchas gracias
    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola,
      no creo que sea necesario generar una macro para tal cosa, sería mucho más sencillo que emplees desde la ficha Datos > grupo Obtener datos externos el Asistente par realizar una importación de una hoja (o varias) del Libro2 al Libro1.. esta conexión la puedes configurar para que siempre actualice los datos al abrir el libro (o cada x segundos /minutos) o sencillamente hacerlo cuando quieras manualmente...
      Emplea la herramienta Microsoft Query.

      Espero haberte orientado.
      Sdls cordiales

      Eliminar
  24. Hola
    como haria para que mediante un formato condicional me diera los duplicados de distinto color por ejemplo si la palabra esta mas de 4 veces(duplicada) entonces que me saque el color de fuente rojo y si es menos de 4 o preferiblemente entre 2 y 4 veces que me lo saque azul no se si es algo basico pero no veo la manera agradeceria mucho la ayuda
    de antemano gracias y un saludo

    ResponderEliminar
    Respuestas
    1. Hola,
      tendrías que aplicar diferentes reglas, una para cada intervalo o color, pero en esencia todas serían similares, es decir, formatos condicionales con fórmula.
      Las fórmulas serían, suponiendo estamos evaluando el rango A1:A10, con todo el rango seleccionado y celda activa A1:
      para color azul
      =CONTAR.SI($A$1:$A$10;$A1)<4
      para color rojo
      =CONTAR.SI($A$1:$A$10;$A1)>=4

      si quisieras definir conmás precisión el intervalo podrías probar con:
      =Y(CONTAR.SI($A$1:$A$10;$A1)>2;CONTAR.SI($A$1:$A$10;$A1)<4)
      Espero te sirva
      Slds

      Eliminar
  25. hola si me sirvio pero como haria para que me queden unicamente de color azul las que esten entre 2 y 4 pero como yo hago me desaparece todo y no me muestra nada de condiciones
    Gracias y un saludo

    ResponderEliminar
    Respuestas
    1. Hola,
      sigue los mismos pasos, pero aplica la última fórmula
      =Y(CONTAR.SI($A$1:$A$10;$A1)>2;CONTAR.SI($A$1:$A$10;$A1)<4)
      y le das el formato de fondo azul.

      Es importante que tengas seleccionado todo el rango de celdas al que quieres asignar el formato condicional, y tengas activa la primera celda, tal cual te comentaba anteriormente.
      Slds

      Eliminar
  26. Gracias por aclararme las dudas ^^
    Un saludo

    ResponderEliminar
  27. Hola
    Tengo unos libros de excel actualizandose automaticamente pero nesecito que solo se actulice la base de datos original la que yo manejo si se cumple la condicion por ejemplo
    que en la una celda de el otro libro se coloque Barbosa y que me actulice todos las celdas del libro principal(el que yo manejo) solamente si se cumple esta condicion
    De antemano muchas gracias
    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola!,
      lo siento pero no entiendo que prentendes...
      Pero si necesitas actualizar datos externos deberás manejar el Editor de vínculos, indicándo cuáles son los vínculos a actualizar.
      También podrías optar, si tienes nociones de VBA, empleando macros
      http://excelforo.blogspot.com.es/2012/10/vba-updatelink-actualizar-vinculos-en.html
      añadiéndole una instrucción IF THEN:
      ...
      IF range("A1").value="Barbosa" THEN
      'esto código actualización'
      END IF
      ...

      Espero te pueda servir.
      Slds

      Eliminar
  28. Hola
    gracias tengo otra duda
    If (TextBox7 = "1") Then
    Sheets("B.D").Select
    tengo esta linea de codigo para que mediante el numero de textbox7 en un formulario previamente llenado me lleve el registro a distintas bases de datos ahora la cuestion es que cuando oculto las hojas de trabajo no me deja ingresar y me pasa a un error que tendria que cambiar para que me dejara ingresar los datos con las hojas ocultas
    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola,
      si la hoja está oculta, primero tendrías que mostrarla, y acabar el código ocultándola nuevamente:
      ....
      'muestra hoja
      Sheets("Hoja2").Visible = True
      '... resto código....
      'oculta hoja
      Sheets("Hoja2").Visible = False
      ....

      Esto sería lo más sencillo.
      Slds

      Eliminar
  29. Saludos!
    comento mi duda:
    en una tabla, usando la funcion SUBTOTALES (9,)
    y filtrando columna mes= 1,ciudad= "Merida", me arroja la suma
    de la comlumna importes (por ejemplo)= $105,949.57
    en otra hoja del mismo libro busco hacer lo mismo )sumar importe segun meses por ciudad) usando la funcion SUMAR.SI.CONJUNTO(), quedando:
    =SUMAR.SI.CONJUNTO('BD2013'!J:J,'BD2013'!O:O,B2,'BD2013'!N:N,1)
    donde BD2013 es nombre la hoja con datos, b2 es la ciudad (Merida) y 1 es la clave del mes y aqui
    me arroja la cantidad de $34,761.64
    -mucha la diferencia no?
    supuestamente deberia darme el mismo importe de venyas para merida en el mes de Enero.
    he revisado una y otra vez los textos, los numeros, etc y no hallo la razon de este error. Agradecere su ayuda.

    ResponderEliminar
    Respuestas
    1. Yo mismo:
      extrañamente se soluciona:
      con la tabla de datos, filtrando a otras ciudades y por ultimo
      volviendo a la ciudad de merida, la formula sumar si conjunto
      en la otra arroja resultado correcto. Gracias¡¡

      Eliminar
    2. Hola,
      claramente tienes algún problema de refresco en tu equipo, de todas formas, si vas aplicar una función SUBTOTALES con suma para una tabla sobre la que aplicarás un filtro, emplea el código 109 com oprimer argumento
      =SUBTOTALES (109;...)
      así te asegurarás que sólo suma los datos visibles en ese momento y no tendrás sorpresas.
      Sin duda SUMAR.SI.CONJUNTO es 100% fiable ya que aplica la suma a los registros que cumplan las condiciones, estén o no a la vista.

      Slds cordiales

      Eliminar
  30. Hola
    soy el de la pregunta de la hoja oculta como haria para que en este caso me quede muy oculta osea que con un click derecho no muestre la opcion que solo la muestre si se va a VBA y se muestre de alli ya que yo la coloco muy oculta mediante VBA pero el codigo me la deja oculta y se puede mostrar nuevamente no es tan trascendente pero me gustaria saber si hay alguna manera
    Un saludo y gracias

    ResponderEliminar
  31. Hola
    a lo que me refirio es que con el codigo de arriba cuando lo ejecuto me coloca las hojas hidden teniendolas veryhidden mediante VBA y quiero saber si hay una forma de dejarlas veryhidden y no hidden
    un saludo

    ResponderEliminar
    Respuestas
    1. ... si asignas una propiedad VeryHidden a una hoja se debe quedar así, y no con 'sólo' Hidden.
      qué código es el que usas??
      Con un código como este (si es que no quieres usar la ventana de Propiedades):
      Sub ocultar()
      Sheets("Hoja1").Visible = xlVeryHidden
      End Sub

      se oculta y queda como 'veryhidden'
      Slds

      Eliminar
  32. Estimado Ismael,
    Después de buen tiempo vengo a molestar de nuevo jeje...bueno en esta oportunidad no sé si es problema de excel o de mi conocimiento limitado, verás, he creado una función (public function) en vba la cual tiene la instrucción de extraer la parte numérica de cualquier cadena ya sea con los caracteres al final o al medio o a ambos lados; esta función funciona pero cuando la cadena tiene mas de 10 dígitos colapsa y así escriba la función de tipo LONG o DOUBLE igual no quiere calcular ya...habrá una solución para esto? o se requiere de una libreria o algo por el estilo (como se hacía en codificación c++)
    Muchas gracias de antemano...Saludos!!!

    ResponderEliminar
    Respuestas
    1. Hola Yuri,
      tendría que ver el código de la función, envíamelo si queires.
      Echa, de todas formas, un vistazo a esta entrada:
      http://excelforo.blogspot.com.es/2012/02/vba-funcion-personalizada-para-agregar.html

      A lo mejor te da una mejor pista. En principio no debería darte error o 'colapsar' para un número de dígitos tal... en definitiva será un tema de precisión numérica, y no creo que esas cadenas tengan una longitud 'numérica' de 26 o más caracteres(me parece que este es el límite numérico de Excel).

      Trabaja mejor quizá con String, en vez de Long o Double, incluso con Variant.

      Ya me cuentas.
      Slds

      Eliminar
    2. Hola Ismael,
      no puedo recordar el código inicial, te lo envío el lunes para ver si me das una mano en descubrir dónde está la falla, pero volví a hacerlo y ahora sale bien, el código es el siguiente:

      Public Function extrae_numero(Valor As String) As String

      Dim n,c As String

      If Len(Valor) > 0 Then
      For f = 1 To Len(Valor)
      c = Mid(Valor, f, 1)
      If IsNumeric(c) Then
      n = n & c
      End If
      Next f
      Else
      c = ""
      End If
      extrae_numero = n

      End Function

      El problema es que a partir de 16 dígitos empieza a fallar aunque no creo que tenga un número de ese tamaño.

      Saludos.

      Eliminar
    3. Hola Yuri,
      correcto, el problema es la precisión y longitud de los número en Excel, que es 16 (lo escribí mal en el comentario anterior).
      Slds

      Eliminar
    4. Hola Ismael,
      el código del que te comenté es el que muestro a continuación:

      Public Function GetNum(Valor As String) As Double

      Dim T, T1, inv As Double
      Dim Ch As String
      i = 1
      T = 0

      For f = 1 To Len(Valor)
      Ch = Mid(Valor, f, 1)
      If IsNumeric(Ch) Then
      T = T + Ch * 10 ^ (i - 1)
      i = i + 1
      End If
      Next f

      For inv = 0 To i - 2
      T1 = T1 * 10 + T Mod 10
      T = Int(T / 10)
      Next inv


      GetNum = T1

      End Function

      La verdad que le di unas vueltas pero no logro ver que está mal. Tengo conocimientos de programación pero este lenguaje recién estoy dominándolo.

      Saludos.

      Eliminar
    5. Hola Yuri,
      prueba con esta función.. es algo más sencilla:
      Public Function SacarNum(strText As String)
      Dim Lista As String
      Lista = [1234567890]
      calc = ""
      For n = 1 To Len(strText)
      calc = calc & IIf(InStr(Lista, Mid(strText, n, 1)) > 0, Mid(strText, n, 1), "")
      Next
      SacarNum = Val(calc)
      End Function

      En tu código me parece (es una sensación) que esa opeación de multiplicar por 10^(i-1) deja fuera del rango algo...???

      Espero te sirva la alternativa.
      Slds

      Eliminar
    6. Hola Ismael,
      Pues sí eso consideré en algún momento, el código que me diste también compila correctamente muchas gracias!!!

      Saludos

      Eliminar
    7. Me alegro te sirva mi propuesta... y que funcione.
      Un cordial saludo!!!

      Eliminar
  33. Hola,
    Me gustaria generar una formula de sumar.si.conjunto que me sume los importes por cada empleado que no tengan fecha de cierre y otra en las que si aparezca fecha.
    No se como indicar en el criterio 2 que esa celda este vacia y de esta manera me sume los importes de un empleado que no tiene fecha de facturas. La otra formula sería identica pero en la que indique que si tiene fecha de cierre de factura. De esta manera saber lo que le debería pagar ya que tiene fecha de cierre y lo que quedaria pendiente ya que no tiene fecha de cierre.

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola, podrías emplear
      =SUMAR.SI.CONJUNTO(rango a sumar;rango empleados;Empleado;Fechas;">"&0)
      al ser las fechas realmente un número, con añadir la condición de ser >0 es suficiente.
      Para saber los que no tienen fecha
      =SUMAR.SI.CONJUNTO(rango a sumar;rango empleados;Empleado;Fechas;"")

      Slds

      Eliminar
  34. podria servir esta formula si tengo una relacion de origen clientes, kilos y fecha y quiero que en otra hoja me vaya sumando los totales actualizados por cliente determinado?

    ResponderEliminar
    Respuestas
    1. Si, claro...
      no importa el origen, si está en una hoja u otra.
      Slds

      Eliminar
  35. Hola que tal, mi problema es el siguiente yo debo introducir en mi tabla quien se va a llevar el premio de puntualidad, tengo a mis empleados por nombre en cada fila, si yo introduzco mis criterios como "PS", "AC", "AS", "AV", "PC" Y "E" asi como cualquier numero (que para mi significa la cantidad de retardos que tiene y por lo tanto no corresponde pago de premio) requiero que una columna simplemente me diga "SI" ó "NO" para facilitar ver quien se lo lleva y quien no. Saludos y gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      necesitaría conocer los criterios que indican si corresponde o no premio.
      Si en esa columna que hablas (pongamos columna B) si existe un número (cualquiera) es que ha tenido un retraso en la puntualidad, y en cualquier otro caso (por ejemplo alguno de esos códigos "PS", "AC", "AS", etc) no hay retraso y opta al premio, entonces una fórmula podría ser en la columna C:
      =SI(ESNUMERO(A1);"NO","SI")
      que dice que si es un número cualquiera entonces devuelve NO, en el resto de casos un SI.
      Slds

      Eliminar
    2. Hola antes que nada deseo expresarte mi sincero agradecimiento por tus respuestas hacia nosotros.

      Volviendo al tema pues mira, solamente ameritarían para pago de premio los empleado que sean clasificados con leyenda "AV" y "E" todos los demás no, incluso los que tengan algún número diferente de "0" (pues estos indican el número de minutos que llegan tarde), ahora, como deseo evaluarle a cada empleado su comportamiento mensual (digamos que ocuparia de la columna A a la P, por ejemplo) para abarcarle todo el mes pues requiero que sea evaluada cada columna, saludos y muchas gracias

      Eliminar
    3. Hola,
      para cada individuo tendrías que añadir esta fórmula, pero suponeindo que tienes una fila de apoyo con todos 1, por ejemplo, si tus datos van de la columna A a la P, en la fila A1:P1 ponemos todos 1, entonces para cada individuo en la columna Q:
      =SI(SUMAPRODUCTO((A2:P2="AV")+(A2:P2="E");A1:P1)>=1;"tarde";"premio")
      que significa que si existe al menos un AV o un E ha llegado tarde, y en cao contrario tendrá Premio.
      Saludos

      Eliminar
  36. Hola Ismael,
    Mi consulta es la siguiente, tengo un libro excel con 25 hojas de trabajo que tienen la misma tabla luego en una hoja de resumen intento utilizar la fórmula sumar si con las hojas agrupadas y me sale el error de #!valor¡, al hacerlo pagina por pagina me da el valor pero me sale una formula inmensa de grande y tendria que hacerlo con muchas celdas... :( me puedes ayudar?

    ResponderEliminar
    Respuestas
    1. Hola Nicole,
      el problema es que no todas las funciones son aplicables a hojas agrupadas (fórmulas 3D), lee este post:
      http://excelforo.blogspot.com.es/2012/02/formulas-3d-tridimensionales-en-excel.html

      La solución es una función personalizada VBA:
      http://excelforo.blogspot.com.es/2014/01/vba-una-funcion-personalizada-de.html

      Saludos

      Eliminar
  37. hola, es la primera vez que hago mi consulta cual seria la formula excata para sumar columnas y celdas combinadas es decir:

    sumar.si.conjunto(d14:d200;d14:d2000;">0";p14:p2000;"1"-------que sume solo los que tengan resultado 1 y de la misma manera los de resultado 2, 3 etc.

    pero pasa que la columna de los resultados (columna p) al tener celdas combinadas,me suma solo la primera celda,y no las demas celdas combinadas, no se si me dejo entender.

    ResponderEliminar
    Respuestas
    1. Hola Erickson,
      el problema de trabajar con celdas combinadas es este que comentas.. siempre trae problemas para poder operar correctamente sobre ellas...
      Estas celdas combinadas sólo deberíamos emplearlas en nuestros modelos en aquellas partes que no influyan en operaciones posteriores.

      Lamentablemente no hay mucho que hacer...

      Slds

      Eliminar