miércoles, 16 de diciembre de 2009

Función CONTAR.SI.CONJUNTO en Excel 2007.

Solicitaba ayuda un lector sobre la manera de CONTAR registros que cumplieran dos condiciones dadas:

...deseo realizar un cuadro donde en una columna escriba los símbolos P que significa promovido, R repite, RR requiere recuperación, etc. como puedo hacer para no contar. además debo contar segun sexo, es decir cuantos hombres y mujeres son promovidos, etc...


Planteando la siguiente tabla de datos:


Para conocer cuantos individuos tienen un determinado 'Sexo' además de tener un 'Concepto' determinado propondría tres posibles soluciones:
  • Usando la función CONTAR.SI.CONJUNTO (sólo para versiones Excel 2007).
    Esta función aplica criterios a las celdas en varios rangos y cuenta cuántas veces se cumplen dichos criterios
    =CONTAR.SI.CONJUNTO(rango_criterio1; criterio1; rango_criterio2; criterio2;...)
    siendo para nuestro ejemplo
    rango_criterio2 = rango de la Tabla donde estén definidos los 'Conceptos'
    criterio2 = lo determinaría el 'Concepto' a analizar
    rango_criterio2 = rango de la Tabla donde esté definido el 'Sexo' de cada individuo
    criterio2 = lo determinaría el 'Sexo' a analizar


    haz click en la imagen


    Vemos que la función nos ha contado cuantos registros cumplen al tiempo un Concepto y un Sexo definido.

  • Usando Tablas dinámicas. Construimos una Tabla dinámica sobre nuestra Tabla de datos y le damos la siguiente estructura de áreas y configuraciones de campo.


  • Empleando una SUMA CONDICIONAL matricial, escribimos la siguiente función:
    {=SUMA(SI($A$2:$A$11=B49;SI($C$2:$C$11=A49;1;0)))}


    conseguimos con esta función matricial que nos sume 1 cada vez que el rango de Concepto sea un valor definido y además el de Sexo coincida con uno dado.


Aunque todas las opciones son válidas, claramente la Tabla dinámica sería la mejor elección, ya que nos actualizará los elementos según aparezcan; mientras con las dos opciones restantes tendríamos que configurar las posibles combinaciones entre ambos campos a estudio de manera manual.

59 comentarios:

  1. sE PUEDEN SUMAR DATOS DESDE DISTINTAS HOJAS??

    ResponderEliminar
  2. Hola, buenos días,
    pues si es posible... puedes ver cómo en
    http://excelforo.blogspot.com/2009/10/sumar-celdas-de-distintas-hojas-en.html

    Espero te sea de utilidad.
    Un saludo

    ResponderEliminar
  3. necesito su ayuda, tengo en varias celdas de la columna a. supongamos que de la A1:A15 y tengo un numero que capture el 400 quisiera saber si se puede hacer que me busque las celdas que se pueden sumar para dar el numero mas proximo a mi resultado o numero que yo cature que es 400

    ResponderEliminar
  4. Hola,
    la cosa es algo complicada... por que no hay una combinación clara de celdas que den el valor buscado; quiero decir, ¿cuántas celdas deben sumar 400?? una, dos, tres,..., las quince???.
    Al no haber una condición clara, yo al menos no sabría como reproducirla desde Excel.
    Lo siento...

    ResponderEliminar
  5. Hola no logro entender los escenarios en excel. Ayuda por fa!!!!!!!

    ResponderEliminar
  6. Hola,
    respecto a los 'Escenarios' de Excel, subí hace algún tiempo un ejemplo de su uso:
    http://excelforo.blogspot.com/search/label/Escenarios

    Recuerda que un 'Escenario' es una situación concreta, con unos valores determinados, para un modelo financiero, económico, matemático, etc.

    Si no lo ves claro, envíame un mail a:
    excelforo@gmail.com

    Slds

    ResponderEliminar
  7. como pongo la formula en ingles

    ResponderEliminar
  8. Hola, buenos días.
    supongo que te refieres a la función CONTAR.SI.CONJUNTO; si es así, la función en inglés es COUNTIFS.
    Espero te sirva.
    Un saludo.

    ResponderEliminar
  9. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  10. Hola:
    Deseo contar los valores que cumplen la condición de estar entre dos números. ¿Cuál sería la sintaxis correcta para la fórmula?

    Ej: tengo mil números que están entre 19 y 31
    deseo saber cuantos hay entre 19 y 21.4.

    gracias anticipadas por la ayuda

    ResponderEliminar
    Respuestas
    1. Hola rgelabertf,
      la función sería:
      =CONTAR.SI.CONJUNTO(rango;">="&19;rango;"<="&21,4)
      Slds

      Eliminar
    2. Hola administrador, me interesa resolver este mismo problema pero con tablas dinámicas.
      Es posible?, gracias

      Eliminar
    3. Hola,
      si es posible. Tendrás que resumir por cuenta, y Agrupar el campo en ese rango.. filtrando luego exclusivamente por éste, eliminando de la vista el resto.
      Espero te sirva
      Slds

      Eliminar
    4. Hola Ismael

      Lo intenté pero algo hago mal que no funciona. Te puedo enviar mi archivo?, a qué dirección?

      Gracias anticipadas.

      Eliminar
    5. Hola,
      sí, puedes enviarlo a
      excelforo@gmail.com

      Slds

      Eliminar
  11. Hola necesito ayuda en el siguiente ejercicio: Realizar una hoja de calculos que permita verificar si un trabajador puede ser condecorado o no, de acuerdo a las siguientes condiciones:
    a. Si tiene mayor o igual a 15años de servicio
    b. ha realizado labores en la comunidad
    c. ha realizado estudio de postgrado.
    Ayudenme por favor Gracias...

    ResponderEliminar
  12. debe ser con la funcion SI ese ejercicio. OJO es una aclaratoria no una pregunta.

    ResponderEliminar
    Respuestas
    1. Hola,
      supondremos que los años de servicio están en la columna A, si ha realizado labores en la comunidad (Si/No) en la columna B, y por último si ha realizado estudios de postgrado (Sí/No) en la columna C.
      Entonces la fórmula sería:
      =SI(Y(A1>=15;B1="Si";C1="Si");"condecorado";"no condecorado")
      Slds

      Eliminar
    2. Muchassss gracias!!!!! Tengo otro que dice Si el promedio de sus (04) notas es mayor que 12 puntos y menor o igual a 16 puntos publicar el mensaje de "Bueno". Como se haria en este caso?

      Eliminar
    3. A ver, se trata de aprender con las explicaciones...no de solucionar cada ejercicio que tengas.
      Este segundo ejercicio es igual al anterior, tienes que usar un condicional SI, y en el argumento de la prueba lógica añadir las dos condiciones >12 y <=16:
      =SI(Y(PROMEDIO(notas)>12;PROMEDIO(notas)<=16);"Bueno";"No bueno")

      Eliminar
    4. Muchas gracias por tu explicacion me sirvio de mucha ayuda, el profesor no nos explico eso. solo los SI sencillos. y nos mando esos ejercicios. Muchas gracias de verdad.

      Eliminar
  13. Hola, me gustaria saber si existe alguna función con la que pueda determinar cuantos elementos existen en una lista, por ejemplo tengo una lista con varios nombres que se repiten y deseo saber cuantos nombres hay.

    ResponderEliminar
    Respuestas
    1. Hola,
      prueba con lo explicado en esta entrada del blog:
      http://excelforo.blogspot.com.es/2011/04/contar-el-numero-de-elementos-unicos-en.html
      Se basa en la función FRECUENCIA.
      Otra forma sería aplicar una Tabla dinámica sobre el listado, y llevar ese campo al área de filas, y ahí ver cuántos registros únicos existen.
      Hay alguna otra manera, pero espero te sirva alguna de estas.
      Slds

      Eliminar
  14. Muchas gracias, es justo lo que estaba buscando

    ResponderEliminar
  15. Hola me gustaria saber que es lo que realiza el simbolo * en una formula matricial, vi si erea un concatenador y no lo es menos multiplica, lo he visto en varios ejercicios pero no se la funcion que cumple , =SUMA(($A$2:$A$20=$B$23)*($B$2:$B$20=$B$24)*$C$2:$C$20, la funcion me summa autos de un determinado color pero no se que realiza el * entre los parentecis y depues del ultimo parentensis... porfaaaa si alguien me puede ayudar se lo agradeceria muchooo !!

    ResponderEliminar
    Respuestas
    1. Hola,
      el símbolo * (asterísco) no es ni más ni menos que un producto, una multiplicación, igual que en cualquier otra fórmula.
      Lo que tiene de especial en una fórmula matricial es que multiplica rangos, de manera 'similar' a como lo hace la función de Excel SUMAPRODUCTO.
      En la fórmula que indicas estarías sumando sólo los registros del rango C2:C20 cuando los valores de A2:A20 coincidan con B23 y a la vez los de B2:B20 con B24.
      Espero haberme explicado.
      Slds

      Eliminar
  16. ooooooooooooooooo valeee muchisimas gracias se pasaron muyy buen blog . se agradece :)

    ResponderEliminar
  17. Buenos días. Excelente blog. Estoy teniendo un problema con el uso de la función CONTAR.SI.CONJUNTO. Me funcionaba bien, cuando estaban todos los datos en una misma hoja, pero dado que debo separar los resultados en varias hojas, necesito que los cálculos se realicen entre diversas hojas. La fórmula que está en la celda es así: =CONTAR.SI.CONJUNTO(Visitas!$C$2:$F$1474;Tecnico!A5;Visitas!$A$2:$A$1474;Tecnico!$E$1)

    ¿Estaré omitiendo algún paréntesis o signo? Muchas gracias por adelantado!

    ResponderEliminar
    Respuestas
    1. Hola Raimundo,
      muchas gracias por tus palabras.
      Respecto a tu cuestión, a simple vista, no hay problema en que los datos a evaluar y las condiciones estén en diferentes hojas (o libros); lo único que veo de raro en tu función:
      =CONTAR.SI.CONJUNTO(Visitas!$C$2:$F$1474;Tecnico!A5;Visitas!$A$2:$A$1474;Tecnico!$E$1)
      es que el primer rango de criterios no es una columna; esto puede jugarte malas pasadas en el resultado de la fórmula, en el mejor de los casos un error de VALOR.
      Espero que sea esto tu error.
      Slds

      Eliminar
  18. Impecable lo tuyo! Era eso, 1.000 gracias!

    ResponderEliminar
  19. hola, yo quisiera hacer lo siguiente:
    Quiero sacar una suma especifica de un rango de numeros, por ejemplo: Si hay 15 numeros y suman en total 50. Yo quiero que aleatoriamente se elijan de los 15 numeros para sumar 25 y se seleccionen los numeros sumados.
    Es para un proyecto de investigacion, muchas gracias
    Esta muy bueno su blog

    ResponderEliminar
    Respuestas
    1. Hola Fernando,
      lo más sencillo sería aplicar Solver, como te muestra este ejemplo del blog, para que te determine cuáles son los valores que te suman esa cantidad (25).
      Echa un vistazo a:
      http://excelforo.blogspot.com.es/2011/10/conciliar-una-partida-con-solver.html

      Creo te puede servir para lo que quieres.
      Slds

      Eliminar
  20. me ha sido de mucha ayuda este foro,tengo una duda con la funsion =CONTARA ya que debo contar celdas donde obtengo un resultado por medio de un hipervinculo, y muchas veces tengo el cero como respuesta, como hago para que no se cuente el cero...?

    desde ya muchas gracias

    ANDRES

    ResponderEliminar
    Respuestas
    1. Hola Andrés,
      me alegra que halles en mi blog algo de utilidad.

      Respecto a tu pregunta creo que una función CONTAR.SI sería más conveniente:
      =CONTAR.SI(rango;"<>0")
      te contará celdas en 'rango' que no sean iguales a cero, como comentabas.
      Slds cordiales

      Eliminar
    2. Muchisimas gracias me ayudo totalmente.
      queria preguntarte 2 cosas mas.
      1ro.- trabajo con un programa que me entrega un dato en este formato: ( 06h45'08'')el cual copio a excel, necesito convertirlo a un formato en el que pueda trabajar en excel, he tratado de utilizar la funsion convertir, pero no me sirve en este caso,tambien he tratado realizar una macro, pero igual no me sale ja ja ja...
      Crèeme solo busco que me guies....

      2do.- trabajo con algunas hojas que tienen datos en comun:

      hoja1: 25 hoja2: a 25 b 32 c 23 d 45
      32
      23
      45

      como esta en el ejemplo, los datos en la hoja 2 estan separados por otro dato, lo que he hecho es: =hoja1(rango), pero son muchos datos, me pregunto si existe una formula, o debo aprender a realizar una macro, no se me ocurre nada....

      cualquier sugerencia me ayudara muchisimo........

      saludos.

      ANDRES

      Eliminar
    3. Hola Andrés,
      respecto a la primera cuestión deberé entender que ese dato viene en un formato tipo texto con todos los caracteres(?)):
      06h45'08''
      por lo que quizá podrías usar funciones de texto tipo IZQUIERDA, DERECHA o EXTRAE para sacar las horas, minutos y segundos, y luego juntarlo todo con una función CONCATENAR o con &. Algo de este estilo:
      =B5&":"&B6&":"&B7
      Luego podrás operar sobre él sin problemas.

      Con la segunda cuestión no puedo ayudarte si no planteas que quieres hacer con esos datos (además de cómo se encuentran los datos)...

      Slds cordiales

      Eliminar
    4. siii mil disculpas.......
      lo unico que deseo es automatizar hojas, que solo tenga que ingresarlos en la primera hoja, y aparescan automaticamente en las otras hojas,para su respectivo calculo, lo que habia hecho es, en la celda que necesito el dato, realizo esto: =hoja1(dato), el tema es que estan en otro orden, y son muchos datos........
      la pregunta es: existe otro metodo con el que pueda hacerlo...?
      creo que no soy muy claro al preguntar.........
      te pido disculpas por eso........
      pero quiero aprender........

      muchas gracias con la aclaracion de las otras funciones.....
      ahorita mismo me pongo a investigar.....
      tienes tutoriales al respecto.?

      muy agradecido

      ANDRES.

      Eliminar
  21. adicional:
    en el formato de arriba es para un calculo de horas

    ResponderEliminar
    Respuestas
    1. Hola Andrés...
      el problema de vincular celdas entre diferentes hojas, mediante alguna fórmula, es encontrar un regla única que funciones siempre para todos los casos.
      Si en tu caso, siempre estamos hablando de vincular de manera transpuesta con intercalación de una celda con otro dato, quizá te pueda servir uno de los últimos comentarios de la siguiente entrada:
      http://excelforo.blogspot.com.es/2010/06/celdas-con-referencia-de-estilo-f1c1.html
      En definitiva se trata de encontrar la fórmula única con la que obtener una secuencia 1,2,3,4, etc para reemplazarla en la fórmula:
      =INDICE(Hoja1!$A$1:$A$4;fórmula;1)
      y que al arrastrarla en vertical en tu hoja2 incluso con esas celdas alternar consigas completar la plantilla.
      Normalmente se suelen emplear combinaciones de la funcion COLUMNA y COLUMNAS.. pero cualquiera vale.

      Slds y ya me contarás.

      Eliminar
  22. Buenas,
    Trabajando con Excel 2003, intento construir una fórmula lógica que me devuelva 1 si se cumple que en un rango de números (A1:A10) hay al menos uno con un valor intermedio entre dos valores dados que tomo de otras celdas (B1, B2). Solo funciona si uso la suma matricial condicionada que tu expones junto con un SI que comprueba si la suma es >0 para devolver el valor 1. Intento ahorrarme el paso de la suma usando directamente el doble SI matricial, que creo debería funcionar devolviendo 1 directamente 1 si se sumple la doble condicion, pero no es así.

    Fórmula que funciona:
    ={SI(SUMA(SI($A$1:$A$10>=B1,SI($A$1:$A$100,1,0)}

    Fórmula que no funciona:
    ={SI($A$1:$A$10>=B1,SI($A$1:$A$10<B2,1,0),0))}

    ¿Por qué no funciona la segunda fórmula más simple?¿cómo lo solucionarías?
    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      trabajar con matriciales es siempre algo abstracto, debes tener en cuenta que tu segunda fórmula matricial
      SI($A$1:$A$10>=B1;SI($A$1:$A$10<=B2;1;0);0)
      lo que genera es un rango 'virtual', compuesto de 1 y 0 según cumplan o no las condiciones dadas (estar entre B1 y B2); por tanto, si quieres saber si existe al menos un valor que cumpla la condición inicial, realmente le debemos exigir y decir que si la suma de todos esos valores 1 y 0 virtuales es mayor o igual que 1, entonces y sólo entonces verifica tu propuesta: me devuelva 1 si se cumple que en un rango de números (A1:A10) hay al menos uno con un valor intermedio entre dos valores dados que tomo de otras celdas (B1, B2)
      Es por esto que tu fórmula matricial podría ser:
      =SI(SUMA(SI($A$1:$A$10>=B1;SI($A$1:$A$10<=B2;1;0);0))>=1;1;0)

      Saludos

      Eliminar
    2. Es decir, que mi segunda fórmula matricial (sin la SUMA + SI), lo que devollvería sería un vector de 1s y 0s, con un valor para cada celda del rango testado del tipo (1,1,0,1,1,0,0), con lo cual no se puede sometar a una función lógica simple del tipo SI y hay que recurrir a la suma de tales valores para generar un escalar que si puede sometarse a la pregunta lógica. ¿Es algo así?

      Eliminar
    3. Perfectamente explicado. Así es.
      Saludos!

      Eliminar
  23. Buenos días, estoy intentando hacer una formula en Excel de varios rangos y en uno de ellos quiero que me cuente si en la columna H, por ejemplo, aparece el numero 6 y el numero 10. Como se puede hacer?, se como se hace para que me cuente si aparece una sola cosa, pero no se como se hace para que me cuente si en la misma columna aparecen dos criterios distintos.
    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      que tal así:
      =CONTAR.SI(H:H;6)+CONTAR.SI(H:H;10)

      Saludos

      Eliminar
  24. Buenas tardes,

    ¿Cómo se puede romper el vínculo a un archivo inexistente?

    ResponderEliminar
    Respuestas
    1. Hola,
      puedes emplear el Editor de vínculos
      http://excelforo.blogspot.com.es/2009/12/modificar-vinculos-en-excel.html
      para Romper el vínculo en cuestión.
      Saludos

      Eliminar
    2. Eso ya lo he intentado, el problema es que al abrir mi libro aparece un cuadro de diálogo con el texto "No podemos actualizar algunos de los vínculos de su libro ahora mismo.
      Puede continuar sin actualizar, o editar los vínculos que crea que son incorrectos" y muestra dos opciones "Continuar" y "Modificar vínculos".

      Al darle "Continuar" el cuadro desaparece pero al volver a abrir el archivo se genera el mismo cuadro de diálogo con las mismas opciones.

      Al seleccionar "Modificar vínculos", me lleva al cuadro de diálogo "Modificar vínculos", donde se presentan 5 opciones y detalla un origen al cual se tiene vínculo. Dicho archivo de origen ya no existe en la computadora, es por ello que al darle "Romper vínculo" no ocurre nada, simplemente se sigue mostrando el cuadro con las 5 opciones y mostrando el mismo origen.

      Quiero saber cómo se pueden romper vínculos a un archivo inexistente.

      Eliminar
    3. Hola Danes,
      correcto, es que esta es la forma.. que el archivo/ruta ya no exista no implica que no se pueda Romper el vínculo desde el cuadro diálogo de 'Modificar vínculos'...
      de hecho, para estar completamente seguro, he replicado el caso, y sin problema me permite romper el vínculo sobre un archivo que ya no existe.

      Quizá el problema no sea que el archivo ya no exista, si no que el vínculo lo tengas en un gráfico, o en un control, o una tabla dinámica, o en un formato condicional.. esto es, en cualquier sitio diferente a una celda...
      ya que en ese caso, exista o no el fichero, no se puede romper el vínculo desde el cuadro 'Modificar vínculos'... debiendo localizar y borrar manualmente el link....

      Un cordial saludo para ti también.

      Eliminar
  25. hola saludos; tengo una pequeña duda que quizá pueda ayudarme a resolver. utilice una función if, para mostrar parámetros numéricos en determinadas celdas partiendo del ingreso de datos alfabéticos en otras. lo hice de este modo.
    =SI(E9="barcelo";"2.550,00";SI(E9="caracas";"1,380,00";SI(E9="maracaibo";"1.500,00";SI(E9="turmerin";"780,00";SI(E9="valencia";"900,00";"0")))))

    todo funciona bien: al ingresar cualquiera de las variables ejemplo "caracas" en la columna E me muestra el valor numero automáticamente en la columna J, el Problema es como totalizar los valores que aparecen luego en la columna J. no he logrado sumar los valores ya que las formulas parten de esa columna. si hago autosuma dice 0 en que puedo estar fallando? agradecería su ayuda gracias.

    ResponderEliminar
  26. Buen dia resiliente.
    Lo que pasa que en los valores que estableciste en las formulas para retornar el resultado los estableciste del tipo texto es por eso que no realiza la suma de valores por que para el excel es solo texto.

    =SI(E9="barcelo";2550;SI(E9="caracas";1380;SI(E9="maracaibo";1500;SI(E9="turmerin";78000;SI(E9="valencia";90000;0)))))

    Prueba asi.
    no es necesario que pongas los datos con separadores de miles y de decimales en la formula. Lo puedes hace desde formato de celdas.
    En la formula solo pon el resultado tal cual.

    ResponderEliminar
    Respuestas
    1. Estimado Gilberto Morales Jeronimo!!! Problema resuelto!; muchísimas gracias por tan eficiente respuesta a mi dilema. que Alegría!!!!! profundamente agradecido.

      Eliminar
    2. Resiliente,
      recuerda como regla general en Excel, los texto entre comillas, lo números y fechas sin ellas...
      por desgracias hay alguna excepción... :(

      Un saludo!

      Eliminar
  27. ESTIMADO AMIGO, NECESITO AYUDA, NECESITO SABER QUE FORMULA SE USA PARA OBTENER EL SEXO DE LAS PERSONAS DENTRO DE LA PLANILLA. APLIQUÉ LA FUNCIÓN SI PERO NO ME SALE. NO SE QUE ESTOY HACIENDO MAL.
    LA APLIQUÉ ASÍ
    =SI(D2="M";"M";"F")
    AL PRESIONAR ENTER TODO ME DA "F" :(

    ResponderEliminar
    Respuestas
    1. Hola,
      todo dependerá de qué tengas escrito en esas celdas.. si todo te devuelve F, asegúrate que lo realmente escrito en cada celda no sea 'M ', i.e., con espacios de más en la derecha del texto.
      Slds

      Eliminar
  28. Hola...
    Tengo un ejercicio donde me piden utilizar alguna funcion de Contar y funciones lógicas para determinar que tipo de oferta se va a aplicar en una compra total. Por ejemplo: que sume primero cuantas ofertas tiene en la cuenta y despues que diga que tipo de paquete se le aplica para determiner su % de descuento.
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      habría que ver la estructura de datos y cómo están distribuidos los datos...
      Pero parece las funciones a emplear podrían ser:
      SI
      CONTAR.SI.CONJUNTO
      ...

      Saludos

      Eliminar
  29. Hola, quería saber si es posible en la formula SUMAR.SI.CONJUNTO se puede establecer como uno de los criterios una referencia a una celda que contiene un texto, de modo que luego se pueda arrastrar copiando la formula y el criterio vaya cambiando. Si es posible como hay que introducir el texto para que no de error? Si se hace con un criterio de números al arrastrar lo va cambiando y aparece bien....y como hacerlo con texto?
    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola Daniel,
      no hay ninguna diferencia a la hora de indicar como criterio una texto o un número.. basta referirlo a la celda que contenga el texto...

      Saludos

      Eliminar

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