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.


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:



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:


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.

166 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
  38. Hola!

    Estoy teniendo un problema con una formula.
    Paso a contarles para ver si me pueden ayudar.

    Estoy contando valores unicos de un rango usando la siguiente formula:
    {=SUMA(SI(FRECUENCIA(SI(LARGO(DEVS!C3:C3911)>0,COINCIDIR(DEVS!C3:C3911,DEVS!C3:C3911,0),""), SI(LARGO(DEVS!C3:C3911)>0,COINCIDIR(DEVS!C3:C3911,DEVS!C3:C3911,0),""))>0,1))}

    El tema es que quiero contar, no solo considerando los valores unicos del rango C3:C3911, sino tambien incluyendo otros criterios.
    Rango criterio: E3:E3911
    Criterio: A4

    Muchas gracias!
    Slds

    ResponderEliminar
    Respuestas
    1. Hola Lucía,
      a veces es más sencillo (mucho más que emplear matriciales) emplear par contar la función CONTAR.SI.CONJUNTO.
      También otra manera es emplear una Tabla dinámica, y visualizar cuantos elementos únicos existen...
      También te podría orientar esta forma
      http://excelforo.blogspot.com.es/2010/11/contar-registros-unicos-sobre-dos.html

      Espero te sirva.
      Saludos

      Eliminar
  39. HOLA

    QUISIERA SABER COMO PODRÍA SUMAR CANTIDADES SI CUMPLIERAN CON 2 CRITERIOS EJEMPLO, TENGO 1 PRODUCTO CON EL MISMO NOMBRE, PERO DIFERENTE PRESENTACION, YO QUIERO QUE ME SUME LA CANTIDAD TOTAL PERO SOLO DEL PRODUCTO QUE TENGA LA PRESENTACION 1X20:

    CANT Descrip PRESENT
    100 BARBIE 1X20
    24 BARBIE 12X1
    200 KEN 1X20
    12 KEN 12X1
    120 BARBIE 12X1
    120 KEN 1X20
    80 PERRO 1X20
    12 PERRO 12X1

    COMO PODRÍA TENER UNA FORMULA QUE ME PUDIERA AYUDAR A CREAR ESTO, PORQUE LO HE ESTADO INTENTANDO PERO NO HE PODIDO LOGRARLO

    ResponderEliminar
    Respuestas
    1. Hola maxima183,
      emplea un
      =SUMAR.SI(rangoPresentación;"1X20";rangoCantidad)
      Saludos

      Eliminar
  40. hola tengo una matriz con barias categorías como por ejemplo, maestro de primera , segunda capataz, etc. estos tiene que dar una prueba, cuando la dan me llega aparte en otra matriz la pregunta es como puedo saber cuales me faltan por rendir por categorías y que la información me la sume ejemplo maestro de primera rendidas 5 por rendir 6 y así sucesivamente, si me puedes ayudar estaría agradecido

    ResponderEliminar
    Respuestas
    1. Hola Fernando,
      puedes replantear tu cuestión y ser algo más claro???
      lo siento pero no entiendo la pregunta
      :(

      Slds

      Eliminar
  41. Si claro es un poco complicado de explicar.
    Tengo una matriz general con un listado de maestros de distintas categorías ej: maestro carpintero de primera, maestro carpintero de segunda, capataz carpintero. son como 60 categorías distintas cada uno tiene que dar una prueba, el resultado de esta prueba me llega en un listado aparte. mi pregunta es como puedo hacer para saber a cuantos maestros les falta rendir esta prueba (comparando esta lista con la matriz general) Te dejo un ejemplo. Ojalas y lo entiendas gracias.
    Ej:
    NOMBRE CATEGORÍA RENDIDA POR RENDIR
    PEDRO MAESTRO CARPINTERO DE PRIMERA 5 6

    ResponderEliminar
  42. El ejemplo se desordena tratare de otra manera
    categoría: maestro carpintero de primera
    prueba rendida : 5
    prueba por rendir: 6
    Osea que de la matriz general en la categoría maestro carpintero de primera 5 personas rindieron la prueba y 6 deben rendirla

    ResponderEliminar
    Respuestas
    1. Hola Fernando,
      se podría aplicar un BUSCARV desde el listado de maestros hacía el listado de resultados de pruebas...
      después de aplicar la función, aquellos que obtengas #N/A serían los que no cruzan.. es decir, aquellos 'maestros' que no han hecho la prueba.
      Espero te oriente.
      Saludos

      Eliminar
  43. Hola Ismael,
    Gracias me resulto pero me genera una lista total no por maestro si quisiera saber a cuantos maestros de una especialidad le falta rendir la prueba. como tendría que hacerlo.
    Genere aparte una columna solo con las CATEGORÍAS y al lado dos columnas una pruebas RENDIDAS y POR RENDIR, es aquí donde quiero que me entregue el número. espero y me entiendas, se complica cuando lo quieres explicar con palabras.

    ResponderEliminar
  44. Hola, te pido me ayudes con un asunto : las celdas A2 =SUMA(A3:A33) y B2=SUMA(B3:B33), luego C2=B2/A2*100. El problema es que excel asume las celdas vacias como cero. Y cuando toda la columna A no tiene datos la suma da como resultado cero y esto no me sirve. Pero es posible que la columna A tenga todos los datos en cero y es un resultado válido A2=0. Como logro que no sume las vacias? o que A2 quede vacía cuando A3:A33 son vacías? es posible que C2 quede en ERROR cuando A2 es vacía y quede en 0 cuando A2 es cero?. Te agradezco tu ayuda pues no he logrado encontrar la respuesta.

    ResponderEliminar
    Respuestas
    1. Hola Ana,
      podrías emplear la función CONTAR.BLANCO para controlar cuántas celdas vacías tienes en el rango...
      algo así, en A2
      =SI(CONTAR.BLANCO(A3:A33)=FILAS(A3:A33);"error";SUMA(A3:A33))

      saludos

      Eliminar
    2. Hola, funciona perfecto, muchas gracias

      Eliminar
  45. HOLA ALGUIEN ME PODRIA APOYAR EN EXPLICARME QUE FORMULA DEBO APLICAR SI QUIERO QUE ME SUME LO DE LA COLUMNA "C", SI LA COLUMNA "D" TIENE CIERTO TEXTO
    =SUMA.SI.$E$9:$E$39=CC7(EL CRITERIO QUE DESEO QUE BUSQUE EN LA COLUMNA D)

    ResponderEliminar
    Respuestas
    1. Buenos días Viridiana,
      solo debes seguir las indicaciones de los argumentos de la función, en tu caso sería:
      =SUMAR.SI($D$9:$D$39;"CC7";$C$9:$C$39)

      saludos

      Eliminar
    2. HOLA ISMAEL MUCHAS GRACIAS POR TU AYUDA, PERO SABES TENGO UN PROBLEMA LO QUE PASA ES QUE ESTOY PONIENDO LA FORMULA TAL CUAL Y NO ME RESPETA LA ULTIMA PARTE NO SE SI TALVEZ ESTE PONIENDO ALGO MAL EN LA FORMULA O QUE PASE ASI ES COMO LA ESTOY PONIENDO: =SUMA.SI($C$7:$C$12;"CC2";$B$7:$B$12)

      Eliminar
    3. ¿no te respeta la última parte???. No entiendo.
      En todo caso, la función es SUMAR.SI... ojo, no SUMA.SI (revisa la sintaxis).
      el primer argumento es donde aparecen todos los códigos..
      el segundo, el criterio buscado
      el tercero donde está lo que queremos sumar.
      Saludos

      Eliminar
    4. LISTO!!!! :)
      MUCHISIMAS GRACIAS EXCELFORO ISMAEL ROMER ME AYUDASTE MUCHO.

      Eliminar
  46. Explique brevemente diferencia entre CONTAR.SI.CONJUNTO y SUMAR.SI.CONJUNTO, alguien quien me ayude

    ResponderEliminar
    Respuestas
    1. Hola, que tal estás?
      un placer saludarte igualmente.
      CONTAR.SI.CONJUNTO como su nombre indica cuenta número de registros que verifican las condiciones dadas, mientras que SUMAR.SI.CONJUNTO acumulan/suman cantidades de un rango cuando las condiciones dadas sobre otros (o el mismo rango) se cumplan.
      Saludos

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

      Eliminar
    3. hola ayudenme tengo un listado de apellidos, pero quiero a traves de formula mostrar los apellidos que no contengan la letra Z, lo estaba haciendo de esta manera ~*z* pero no me sale ayudaaa por favor

      Eliminar
    4. Hola Luis Miguel,
      entiendo que me hablas en el contexto de un Autofiltro.
      Si es así puedes aplicar un Filtro de Texto > No contiene: z dentro las opciones de filtro del campo.
      Si hablas de aplicar alguna fórmula aplica la fórmula:
      =(ESERROR(HALLAR("z";A2)))
      cuando devuelva VERDADERO será un apellido sin z

      Saludos

      Eliminar
  47. Hola, tengo una consulta, estuve buscando una función para sumar celdas por color de fondo, la encontré y me funciona perfecto, es la siguiente:

    Function SumarColor(color As Range, rango As Range)
    'color: La celda que contiene el color a sumar
    'rango: El rango de celdas a considerar en la suma

    Dim resultado 'Almacenará el resultado de la suma
    Dim celda As Range

    'Recorrer cada celda del rango
    For Each celda In rango
    'Sumar si el color de la celda es igual al color especificado
    If celda.Interior.ColorIndex = color.Interior.ColorIndex Then
    resultado = resultado + celda.Value
    End If
    Next celda

    SumarColor = resultado
    End Function

    Ahora la duda es que le quiero agregar otro criterio, necesito que me sume las celdas de un determinado color, siempre y cuando en otra columna de la tabla diga "OK", traté de modificarla pero aún no doy con eso. Me podrías ayudar ? La idea es tratar de simular la función sumar.si.conjunto.
    Gracias!!

    ResponderEliminar
    Respuestas
    1. Hola Diego,
      tendrías que incorporar entonces un 3er y 4o argumento en tu función para indicar el segundo rango de evaluación y su criterio, e incorporarlos en el bucle y la sentencia IF...
      o bien añadir una condición AND dentro del IF..THEN
      If celda.Interior.ColorIndex = color.Interior.ColorIndex AND celda.offset(0,0).value="OK" Then
      resultado = resultado + celda.Value
      End If
      ...
      Saludos

      Eliminar
    2. Ismael, gracias por la ayuda.
      Te mande un correo. Por si me puedes explicar mejor.

      Saludos y gracias.

      Eliminar
    3. Si necesito sumar un rango de un color y que tambien sean valores mayores que 0 como seria?

      Eliminar
    4. Hola, qué tal?
      un placer saludarte
      Podrías añadir otra condición AND dentro del IF
      celda.value>0

      Saludos

      Eliminar
  48. Hola Ismael,

    necesito hacer un cálculo que parece que demanda un SUMAR.SI pero no acabo de lograrlo.
    Te pongo el ejemplo:
    A B C
    1 1256 7
    2 1256 30
    3 1260 15
    4 1262 6
    5 1262 15

    Lo que necesito es que Excel me sume en la columna C el las cantidades de la columna B, siempre y cuando la columna A se repita. Es decir, quedaría así:

    A B C
    1 1256 7 37
    2 1256 30 37
    3 1260 15 15
    4 1262 6 21
    5 1262 15 21

    Se trataría de que para C1 busque si en el rango A se repite el valor de A1 y sume el valor de B de cada una de las filas con A repetido.
    Y así sucesivamente para C2, C3,...
    Lo mire por donde lo mire, parece que me meto en una especie de bucle y no encuentro la salida.

    Gracias de antemano.

    ResponderEliminar
    Respuestas
    1. Hola,
      si comprendo bien en toda la columna C quieres obtener la suma acumulada de los valores coincidentes de A, si es así, efectivamente un SUMAR.SI te puede resultar, sería en C1:
      =SUMAR.SI(A:A;A1;B:B)
      y arrastrar...
      otra posibilidad es construir una tabla dinámica sobre tu rango de datos, y llevar la columna A al área de filas y la columna B al área de valores resumido como suma.
      Saludos

      Eliminar
    2. Hola gracias,

      pues no consigo hacerlo funcionar.
      No se si podría hacerte llegar un pantallazo o un archivo para ver si estoy haciendo algo mal (seguro)



      Gracias de nuevo

      Eliminar
    3. Puedes enviarlo a
      exelforo@gmail.com
      Saludos

      Eliminar
    4. Muchas gracias. Lo de las 'referencias absolutas' se escapaba a mis conocimientos; y eso que parece básico. Solo me falta averiguar como se ponen sin tener que teclear manualmente los $$$

      Muchísimas gracias.

      Eliminar
    5. Sitúate en la barra de fórmulas encima de las referencias y pulsa la tecla de función F4 repetidamente hasta conseguir la combinación de referencia relativa-mixta-absoluta que necesites en cada momentos
      Saludos

      Eliminar
  49. Cordial saludo. Quisiera saber lo siguiente: Cuando coloco condicionales de límites en valor siempre tengo que colocar entre comillas la condicion y el valor ejemplo (">=10500"). no es posible que esta condicion o el valor esté refernciado a una celda, es decir, algo como ">=B50". Si es posible cual sería la forma correcta de escribirlo? Mucha Gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      si es posible.
      Imagina escrito en la celda A1 el texto: >=10500
      en tu fórmula, por ejemplo, =SUMAR.SI(B1:B10;A1;C1:C10)

      Otra forma, en A1 tienes el valor 10500, entonces:
      =SUMAR.SI(B1:B10;">="&A1;C1:C10)

      O bien como tu indicabas
      =SUMAR.SI(B1:B10;">=10500";C1:C10)

      Son todas formas correctas
      Saludos

      Eliminar
  50. hola! tengo dos libros y necesito sumar dos columnas y pero para sumarlas tienen que coincidir otros dos campos.

    ResponderEliminar
    Respuestas
    1. Hola Adriana,
      al tener orígenes en dos libros distintos tendrás que componer una fórmula del tipo:
      =SUMAR.SI.CONJUNTO(...)+SUMAR.SI.CONJUNTO(...)
      cada una de ellas sobre un libro y aplicándole las condiciones que necesites.
      Saludos

      Eliminar
  51. Buen día,
    Al realizar una tabla dinámica donde tengo localidades, productos en las filas, pero requiero listarlas por meses, al generar la tabla dinámica me lista los meses desordenadamente como lo corrijo
    gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      probablemente los meses los esté tomando como texto, y no como fecha...
      asegúrate que las Fechas (y los meses) están introducidos como Fecha, si quieres trabajar con meses agrupados, selecciona las Fechas y agrupa en la tabla dinámica.

      Saludos

      Eliminar
  52. Saludos. Estoy usando la función sumar.si.conjunto pero me encuentro con un problema. En el caso de no darse los criterios quiero que el resultado sea una celda vacía y ahora mismo me sale 0. Que puedo hacer?. Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      podrías anidar el SUMAR.SI.CONJUNTO dentro de un SI condicional, con prueba lógica las mismas que la función SUMAR.SI.CONJUNTO, algo así:
      =SI(Y(condicion1;condición2;...);"";SUMAR.SI.CONJUNTO(rngsuma;rngCrit1;condicion1;rngCrit2;condicion2;...))
      Saludos

      Eliminar
  53. Saludos tengo un problema con tres condiciones: tengo que sumar una celda hasta cuatro para el primer mes, luego en la misma celda que cambie al siguiente mes con el mismo criterio hasta llegar a 12 mes, he intendo con esto:

    MES 1
    C=SEMANA1

    k1 M1 N1 L1
    semana 1 semana2 semana3 semana4

    k1 m1 n1 L1
    500 100 50 150

    HE USADO SUMAR.SI.CONJUNTOS
    SUMA.SI
    IF
    PERO NINGUNAS FUNCIONAN

    ResponderEliminar
  54. PERDON, LOS VALORES SON EN LA CELDAS 2, ES DECIR, K2, M2, N2, L2

    ResponderEliminar
    Respuestas
    1. Hola,
      no termino de comprender del todo qué necesitas.. pero parece necesitas una suma acumulada dinámica por meses...
      Quizá este post te de algunas pistas:
      http://excelforo.blogspot.com.es/2012/01/una-variante-de-suma-acumulada-en-excel.html
      Slds

      Eliminar
    2. Gracias Ismael, Acabo de leer el post y probar las formulas pero no me sale. Te envie el archivo por correo para ver si se puede realizar. Se llama: SUMA DE SEMANAS POR MESES SEPARADOS.

      Agradecere mucho tu ayuda

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

    ResponderEliminar
  56. Hola,

    Quiero sumar hasta 30 celdas de una misma fila y que cumplan una condición. De modo que sería algo así:

    A B C … Z
    1 CECO1 100 200 100 500
    2 CECO2 200 50 20 450

    770 CECO770 300 205 220 100

    Para cada CECO quisiera sumar, en otra hoja del mismo archivo, todas las columnas de B a Z.
    ¿Cuál es la forma más eficiente mediante formula de hacerlo?

    Muchas gracias,

    ResponderEliminar
    Respuestas
    1. Hola,
      lo más sencillo sería que en la columna AA realices la suma para cada fila/CECO
      y luego en las diferentes hojas plantees un =SUMAR.SI(Hoja1!A:A;"CECOx";Hoja2!AA:AA)
      ajustando los rangos preferiblemente
      ;-)
      Saludos

      Eliminar
  57. Que tal; necesito sumar las celdas con texto de una columna si son del mismo mes escrito en otra celda de la misma fila. Ejemplo:
    A. B. C.
    1. 16/06/2016. FB076. 3
    2. 17/03/2016 RS561.
    3. 3/06/2016. XV123
    4. 8/06/2016. 674A56F
    5. 03/02/2016. 578457

    Celda "C1" es dónde quiero colocar el resultado, en este caso corresponde al 6o mes

    ResponderEliminar
    Respuestas
    1. Hola Oscar,
      entiendo te refieres a CONTAR el número de celdas del mismo mes:
      =CONTAR.SI.CONJUNTO(A:A;">="&FIN.MES(A1;-1)+1;A:A;"<="&FIN.MES(A1;0))

      Saludos

      Eliminar
  58. Buenas noches...
    Como puedo sumar en un rango valores negativos, si dentro de dicho rango tambien dispongo de valores positivos? El caso es que es un contro de inventarios, en donde en una tabla aparte se esta consolidando total de entradas y salidas del producto 1, total de entradas y salidas del producto 2 y asi sucesivamente. Para ello ello estoy utilizando la función sumar.si.conjunto
    Me aplico muy bien a la hora de calcular los valores positivos del rango, pero al aplicar la formula para lo que corresponden a las salidas(valores estan precedidos de "-") no me funciono...

    Muchas gracias por la ayuda...

    ResponderEliminar
    Respuestas
    1. Hola,
      si solo quieres sumar valores negativos de un rango puedes aplicar:
      =SUMAR.SI(rango;"<=";rng_suma)

      Un saludo!

      Eliminar
    2. Muchas gracias por su valiosa respuesta.
      Asi como ud. dice, funciona suma perfectamente los números negativos del rango. Pero no solo requiero sumarlos, adicionalmente requiero que valide 2 criterios más. Por ejemplo: que sean producto 1 y que equivalgan a salidas.

      Quedo atenta a su valiosos aportes. Mil gracias!

      Eliminar
    3. Hola,
      en ese caso deberás emplear:
      =SUMAR.SI.CONJUNTO(rng_suma;rng_importes;"<0";rng_pdtos;"pdto1";rng_estado;"salidas")

      Espero te oriente
      Saludos

      Eliminar
  59. por favor me pueden ayudar
    24974 33
    25006 50
    25006 50
    24974 33

    que esta suma solo sume el valor 1 vez de la columna repetida, que el resultado sea 83

    ResponderEliminar
    Respuestas
    1. Hola,
      si tienes los datos en A1:B4, entonces en D1:
      =SUMAPRODUCTO(1/CONTAR.SI(A1:A4;A1:A4&"");B1:B4)
      Saludos

      Eliminar
  60. Buenas tardes hago una consulta, como haría si tengo 10 condiciones con colores distintos y solo se cumplirá 1, como la formularia?, de antemano gracias por la ayuda

    ResponderEliminar
    Respuestas
    1. Hola,
      cuando hablas de colores hablas de texto o de formato de color??
      En todo caso, por favor, puedes extender algo más tu cuestión?

      Gracias y un saludo

      Eliminar
  61. Saludos, necesito ayuda con el siguiente calculo de cuota:
    tengo una tasa de 5.63% que multiplicada por 969.60 me da 52.36, y multiplicada por 404.96 me da 22.81, y por 1,308.13 me da 73.69. pero la instrucción que tengo es aplicar una cuota minima de 30 y una maxima de 100 al resultado. he utilizado varias condiciones pero la verdad que no logro resolverlo.

    ResponderEliminar
    Respuestas
    1. Hola,
      suponiendo la cantidad en A1 y la tasa en B1:
      =SI(A1*B1>100;100;SI(A1*B1<30;30;A1*B1))

      Saludos

      Eliminar
  62. Hola, se les ocurre como a partir de la matriz en la cual los titulos de columnas y de filas pueden repetirse en varias columnas y filas, hacer una matriz resumida que responda a la suma de cada celda con el mismo título de fila y columna?..., no se si se entiende bien la pregunta
    Un ejemplo corto sería:
    COL
    FILA c1 c2 c1 c3 c2 c1
    f1 1 1 1 1 1 1
    f2 1 1 1 1 1 1
    f3 1 1 1 1 1 1
    f3 1 1 1 1 1 1
    f1 1 1 1 1 1 1
    f2 1 1 1 1 1 1
    f1 1 1 1 1 1 1

    Para lograr el siguiente resumen
    COL
    FILA c1 c2 c3
    f1 9 6 3
    f2 6 4 2
    f3 6 4 2
    Obviamente en vez de unos, los números internos de la matriz serían cualquiera.

    ResponderEliminar
  63. Respuestas
    1. Hola Benjamin,
      interesante cuestión.
      Subiré un post explicándolo.
      Te adelante que se necesitará una matricial que combine SUMA y SI (dos veces: una para fila y otra para columna)
      Slds

      Eliminar
  64. Hola tengo una hoja que se le van colocando datos cada dia, o sea tiene muchas celdas vacias, al aplicarle una funcion de sumar si conjunto no me funciona si las celdas estan vacias, que debo colocar a la formula para que funcione, gracias.

    ResponderEliminar
    Respuestas
    1. Hola Guillermo,
      que los rangos que marcas dentro de la función (de esta o cualquier otra) no influye si están o no vacías... esto es, si están vacías no sumarán o no aplicarán las condiciones...

      No comprendo a qué te refieres con que 'no funciona'??

      Slds

      Eliminar
  65. Una consulta,
    Quiero usa esta función pero el asunto es el siguiente
    Rango_Suma: A1:D1 celda vacía luego esta F1:I1 debajo estaría el
    Rango_Criterio1 pero el rango de A2:D2 están combinadas las celdas y pasa lo mismo con F2:I2. El criterio es "X" pero la función no incluye todas las celdas que abarca el Rango_Criterio1. Leía que debo descombinar ya que solo reconoce en este caso A2 y F2 excluyendo las demas celdas que deseo que sume. El problema es que necesariamente debo combinar la celda donde esta el Rango_Criterio1, existe algún truco para que incluya todas las celdas..?
    Gracias de antemano por tu ayuda
    Wilian R C

    ResponderEliminar
    Respuestas
    1. Hola Willian,
      pues no hay un truco sencillo.. o bien emplear una función con programación, o bien intentar componer una fórmula matricial compleja...
      Lo más simple sería añadir una nueva fila (fila 3) donde desagregues las celdas combinadas.

      En todo caso, cuando me sea posible trataré de publicar las opciones comentadas

      Saludos

      Eliminar
    2. Listo, gracias de todas formas. Intenteré hacerlo así como lo mencionas.

      Eliminar
  66. =SUMAR.SI(E7:E11,"<>*?",F7:F11)
    =SUMAR.SI(E7:E11,"<>""",F7:F11)
    =SUMAR.SI(E7:E11,"<>*",F7:F11)
    Ayuda, lo que deseo es restringiendo una columna solo considere las celdas no vacia y suma la columna deseada, la columna F varia constantemente, por ello lo quiero en formula, lo que puse es practico pero ambas columnas en mi caso son miles de códigos, podría obtener la suma filtrandolo pero lo quisiera en formula.
    espero su respuesta

    ResponderEliminar
    Respuestas
    1. Hola,
      solo te quedaba poro probar la buena:
      =SUMAR.SI(E7:E11,"<>",F7:F11)

      ;-)
      Saludos

      Eliminar
  67. Buenas tardes, quien me podria colaborar, es que ando ando haciendo un sumar.si.conjunto y cuando despliego la formula para varias casillas me queda el primer valor repetido en todas las otras??

    ResponderEliminar
    Respuestas
    1. Hola!
      ¿¿?? puedes mostrar cómo tienes montada tu fórmula ¿¿??

      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.