none
CONTAR.SI.CONJUNTO IGUAL A CERO (0)

    Question

  • Buenos dias. Resulta que estoy trabajando con dos hojas dentro de un libro y requiero tener un total de datos contados dependiendo de varios criterios para lo cual contar.si.conjunto me iría perfecto. Resulta que si pongo la función con un criterio me cuenta "bien", si lo hago con el otro criterio igual cuenta "bien" pero si los pongo juntos ya no me cuenta bien y entonces me da cero (0). Para las formulas expresadas a continuación que están en la hoja 2; la celda E1 contiene un valor (1) que quiero contar en la hoja 1, en la celda A2, un código que tengo en una columna y que lo tomo como criterio. Este código lo he generado buscando tener un creterio adicional que resulta de tener un rango entre fechas.

    = CONTAR.SI.CONJUNTO(Hoja1!$B:$L;A2) da 1 y es correcto; = CONTAR.SI.CONJUNTO(Hoja1!$B:$L;E1) da 146 y es correcto,

    =CONTAR.SI.CONJUNTO(Hoja1!$B:$L;E1;Hoja1!$B:$L;A2) en una celda y el resultado es cero. Si lo hago de otra forma...

    =CONTAR.SI.CONJUNTO(Hoja1!$B:$H;E1;Hoja1!$B:$H;">=01/01/2001";Hoja1!$B:$H;"<=31/01/2001") igual da cero.

    Cual puede ser mi error?...

    Gracias...

    Friday, September 21, 2012 4:54 PM

Answers

  • Hola Lefromero, bueno ahora si que creo tenerlo claro y espero tener la solución.

    Tienes razón, a medias, con tu afirmación de:

    >> ya que creo es una condición de Contar.si.conjunto que los rangos para las referencias han de ser los mismos. <<

    O sea con un ejemplo creo que me entenderás mejor. Vamos a contar los valores 3 que hay en la columna C, esta fórmula es buena (comprobada):

    =CONTAR.SI.CONJUNTO(B4:B4000;">="&A2;B4:B4000;"<="&A3;C4:C4000;E1)

    en E1 tenemos el valor a buscar.

    Como verás los rangos son distintos pero funciona. Lo que han de ser IGUALES son las "filas" de los rangos, o sea no puedes poner A4:A4000 y C4:C3999 porque dará error.

    El problema con tu hoja es que tenemos que contar las coincidencias en el rango C4:H2000 y esto de forma directa no nos lo permite esta función. Podríamos hacer fórmulas individuales para cada columna de la C a la H e irlas sumando, el resultado será correcto pero la fórmula excesiva (la suma de las seis fórmulas juntas).

    Te propongo utilizar la versátil e incombustible SUMAPRODUCTO, en tu caso la fórmula es:

    =SUMAPRODUCTO((B4:B4000>=A2)*(B4:B4000<=A3)*(C4:H4000=E1))

    también está comprobada.

    Confio en que te sirva y si necesitas algo más sobre esta hoja intentaré ayudarte.

    Saludos,

    Xavier

    Friday, October 12, 2012 8:21 PM

All replies

  • Ver la información en Excel (F1) sobre la función que indicas, contar.si.conjunto, te aclara tu problema claramente, incluso aparece un ejemplo. yo en tu lugar haría un:

    =CONTAR.SI(Hoja1!$B:$H;E1)+CONTAR.SI(Hoja1!$B:$H;">=01/01/2001")+CONTAR.SI(Hoja1!$B:$H;"<=31/01/2001")

    Un saludo y espero haberte podido ayudar.


    Working Into Binary (WIB) - http://workingintobinary.blogspot.com.es/

    Saturday, September 22, 2012 4:12 PM
  • Hola LEFROMERO, para ayudarte sería necesario que pusieras que datos tienes y que quieres obtener.

    En tu tercera fórmula "pides" que te cuente las celdas que cumplen con dos criterios a la vez (?) A menos que en E1 y A2 tengas el mismo valor ¿qué piensas conseguir?

    En la cuarta ¿E1 es una fecha? y si lo es... (?)

    En todas las fórmulas utilizas el total de las columnas, lo que puede ser una barbaridad.

    Por eso te digo que nos des los datos y lo que necesitas.

    El consejo de Albert es bueno, leer la ayuda de la función.

    Saludos,

    Xavier

    Monday, September 24, 2012 10:26 AM
  • Hola Albert, la verdad es que la formula como la planteas no me serviría porque me interesa que cuente bajo las tres condiciones como si fuese una and de 3 entradas; es decir, cuente si cumple la condición que sea el valor de E1, pero si cumple también con la condición que se encuentre comprendido entre las fechas 01/01/2001 y 31/01/2001.

    Lefromero

    Wednesday, October 03, 2012 4:48 PM
  • Xavi, tengo una columna que indica la fecha en que se ha tomado el dato. Luego en seis columnas tengo diferentes valores. Mi idea es hacer que cuente un rango de celdas comprendida entre estas columnas pero que únicamente cuente aquellas que se encuentran comprendidas entre "un rango de fecha para mi caso de ejemplo 01/01/2001 y 31/12/2001", adicional a esto, las celdas que contengan el valor que le indico. Para esto teóricamente me serviría la función contar.si.conjunto porque tengo rango1 = rango2 = rango 3 y mis condiciones son criterio 1 = "<=31/12/2001", criterio2 =">=01/01/2001" y criterio 3 = E1. El valor contenido en la celda E3 sería mi criterio 3. Si hago cada una por separado pues me entrega el valor para cada caso es decir, si hago rango1 y criterio 1 me da p.ej. 100, si lo hago rango2 y criterio 2 me da p.ej. 15 y si hago rango 3 con criterio 3 da 200. Si uno todo esto en un solo contar.si.conjunto me da cero valor que es un error, si lo hago manualmente me da p.ej. 15 que sería lo correcto. Es como si contar.si.conjunto no me permitiese unir las 3 o haya de cambiar algún parámetro. En cuanto a las 3 fórmulas expresadas en mi pregunta inicial, son ejemplos del desarrollo pero buscan explicar lo mismo que digo aquí.


    Lefromero

    Wednesday, October 03, 2012 5:01 PM
  • Hola, en tu primer mensaje dices, entre otras cosas:

    >>>>>>>>>>>>>>

    =CONTAR.SI.CONJUNTO(Hoja1!$B:$H;E1;Hoja1!$B:$H;">=01/01/2001";Hoja1!$B:$H;"<=31/01/2001") igual da cero.

    <<<<<<<<<<<<<

    En primer lugar no deberías utilizar los rangos completos $B:$H porque es una pérdida de recursos hacer que cuente 7 millones de celdas cuando tienes... no creo que tengas esa cantidad. Puedes poner un número que sobrepase ampliamente, si quieres, tus espectativas de rellenar pero yo no soy partidario de utilizar toda la columna, aunque funciona, evidentemente.

    En segundo lugar si los tres rangos son idénticos ¿cómo puedes poner tres críterios distintos?

    Supongamos que tienes las fechas en la columna A de A2:A5000 y que tienes nombres, valores o referencias en la columna B de B2:B5000, en E1 un criterio, con un dato que está contenido en la columna B, la fórmula sería:

    =CONTAR.SI.CONJUNTO(B2:B5000;E1;A2:A5000;">=01/01/2001";A2:A5000;"<=31/12/2001")

    Las fechas que ponemos como criterio están en el formato: dd/mm/aaaa, las que hay en la columna A han de ser con idéntico formato.

    Esta fórmula funciona, comprobado.

    En tu ejemplo se supone que tu utilizas una hoja del mismo libro pero diferente a la Hoja1, en la que están los datos, y que el criterio E1 está en esa hoja, no en la Hoja1.

    Saludos,

    Xavier


    • Edited by Icaro_cat Wednesday, October 03, 2012 8:10 PM
    Wednesday, October 03, 2012 8:04 PM
  • Hola Icaro_cat, explico mejor el asunto, modificando la tabla y exponiéndolo de otra manera. Digamos que estoy haciendo un análisis con el euromillón. Tengo en las casillas:

    A--> Fecha de juego; B--> Sorteo; C... H-->Digito 1 al 6. Los datos están almacenados entre las celdas A4 a H2000. En las celdas A2 escribiré una fecha inicial, en la celda A3 una fecha final, en la celda E1 un dígito. Las fechas inicial y final es para establecer un rango para validar mis datos y entonces presento a continuación lo que obtengo:

    Los datos en las celdas para todo lo que resta serán A2:01/01/2004; A3:31/12/2006; E1: 3 (Deseo saber cuantas veces ha caído el 3 en cualquier posición o dígito "del 1 al 6"...).

    Para la fórmula: =CONTAR.SI.CONJUNTO($A$4:$H$4000;">="&$A$2), el resultado es 913. Es un dato correcto.

    Para la fórmula: =CONTAR.SI.CONJUNTO($A$4:$H$4000;">="&$A$2;$A$4:$H$4000;"<="&$A$3), el resultado es 213. Es un dato correcto

    Para la fórmula: =CONTAR.SI.CONJUNTO($A$4:$H$4000;$E$1), el resultado es 177. Es un dato correcto.

    La fórmula 2 complementa la primera para establecer un rango de fechas entre 01/01/2004 y 31/12/2006. Luego la tercera únicamente establece cuantas veces aparece el 3 "03" en toda mi tabla o base de datos.

    Si complemento la segunda con la tercera, ya tengo problemas, intentandolo de diferentes formas obtengo un dato errado...

    =CONTAR.SI.CONJUNTO($A$4:$H$4000;">="&$A$2;$A$4:$H$4000;"<="&$A$3;$A$4:$H$4000;"="&$E$1) resultado cero (0), es un error

    =CONTAR.SI.CONJUNTO($A$4:$H$4000;">="&$A$2;$A$4:$H$4000;"<="&$A$3;$A$4:$H$4000;"<="&$E$1) resultado cero (0) es un error

    =CONTAR.SI.CONJUNTO($A$4:$H$4000;">="&$A$2;$A$4:$H$4000;"<="&$A$3;$A$4:$H$4000;">="&$E$1) resultado 313 es un error

    =CONTAR.SI.CONJUNTO($A$4:$H$4000;">="&$A$2;$A$4:$H$4000;"<="&$A$3;$A$4:$H$4000;$E$1) resultado cero (0), es un error

    =CONTAR.SI.CONJUNTO($A$4:$H$4000;">="&$A$2;$A$4:$H$4000;"<="&$A$3;$A$4:$H$4000;3) resultado cero (0) es un error

    =CONTAR.SI.CONJUNTO($A$4:$H$4000;">="&$A$2;$A$4:$H$4000;"<="&$A$3;$A$4:$H$4000;"="&$E$1)


    Lefromero

    Thursday, October 11, 2012 2:14 PM
  • Hola lefromero, vamos a dividir el problema por fragmentos para que yo sea capaz de entenderlo.

    Tu dices:

    >> Para la fórmula: =CONTAR.SI.CONJUNTO($A$4:$H$4000;">="&$A$2) <<

    y yo pregunto si en A2 tienes la fecha de incio, o sea una fecha (supongo que en formato Excel, dd/mm/aaaa) ¿para que contar sobre todos los registros [$A$4:$H$4000]?, ¿no hemos quedado que las fechas las tienes en la columna A?, ¿o quizá es en la columna B donde titulas "Sorteo"?, ¿tienes fechas en otras columnas que no sean A o B?, ¿por qué?

    Esa fórmula te dice, según tu correctamente, que hay 913 FECHAS mayores o iguales a la de A2.

    Con la segunda proposición, fórmula, ocurre lo mismo.

    ¿En C4:H4000 tienes un solo dígito por celda?

    Vuelvo a insistir, la busqueda de las fechas se ha de hacer sobre la columna de las fechas, no sobre todas las columnas, y la de los dígitos en las columnas con dígitos, no con dígitos y fechas.

    ¿Lo aclaras?

    Gracias, saludos,

    Xavier

    Thursday, October 11, 2012 4:07 PM
  • Buenos dias Icaro.

    La disposición que tengo para este ejercicio es la siguiente: Celda A4 (en adelante A5,A6,... ) --> Fecha de juego "en formato dd/mm/aaa"; B4 (en adelante)--> Sorteo; C4... H4 (en adelante)-->Digitos 1 al 6; le llamo dígito pero en realidad son números comprendidos entre 1 a 31 y en cada celda hay una valor. Los datos están almacenados entre las celdas A4 a H2000. En las celdas A2 escribiré una fecha inicial 01/01/2004 y en la celda A3 una fecha final 31/12/2006, las fechas están en formato "fecha dd/mm/aaaa"; en la celda E1 un dígito. Las fechas inicial y final son necesarias para establecer un rango "entre fechas" que en este caso quiero establecer los datos entre el 01 de enero de 2004 y el 31 de diciembre de 2012. En la celda A1, he ingresado la primera fórmula que indica el número de fechas encontradas posteriores o iguales a la fecha indicada en la celda A2 o posteriores al 01/01/2004. En la celda B1, he ingresado la segunda fórmula que indica el número de fecha encontradas entre "las fechas contenidas en las celdas A2 y A3" o lo que es lo mismo las fechas encontradas entre el periodo Enero 01 de 2004 y 31 de diciembre de 2006.  La celda C1 contiene la tercera fórmula que indica cuantos valores como el contenido en la celda E1 tengo. En la celda B2, la he utilizado para ingresar cada una de las fórmulas que me generan el error.  

    Me queda la inquietud de lo que dices "Vuelvo a insistir, la busqueda de las fechas se ha de hacer sobre la columna de las fechas, no sobre todas las columnas" ya que creo es una condición de Contar.si.conjunto que los rangos para las referencias han de ser los mismos.

    Gracias Xavi por tu interés, espero podamos despejar estas dudas, cordial saludo

    Luis.


    Lefromero

    Friday, October 12, 2012 2:44 PM
  • Hola Lefromero, bueno ahora si que creo tenerlo claro y espero tener la solución.

    Tienes razón, a medias, con tu afirmación de:

    >> ya que creo es una condición de Contar.si.conjunto que los rangos para las referencias han de ser los mismos. <<

    O sea con un ejemplo creo que me entenderás mejor. Vamos a contar los valores 3 que hay en la columna C, esta fórmula es buena (comprobada):

    =CONTAR.SI.CONJUNTO(B4:B4000;">="&A2;B4:B4000;"<="&A3;C4:C4000;E1)

    en E1 tenemos el valor a buscar.

    Como verás los rangos son distintos pero funciona. Lo que han de ser IGUALES son las "filas" de los rangos, o sea no puedes poner A4:A4000 y C4:C3999 porque dará error.

    El problema con tu hoja es que tenemos que contar las coincidencias en el rango C4:H2000 y esto de forma directa no nos lo permite esta función. Podríamos hacer fórmulas individuales para cada columna de la C a la H e irlas sumando, el resultado será correcto pero la fórmula excesiva (la suma de las seis fórmulas juntas).

    Te propongo utilizar la versátil e incombustible SUMAPRODUCTO, en tu caso la fórmula es:

    =SUMAPRODUCTO((B4:B4000>=A2)*(B4:B4000<=A3)*(C4:H4000=E1))

    también está comprobada.

    Confio en que te sirva y si necesitas algo más sobre esta hoja intentaré ayudarte.

    Saludos,

    Xavier

    Friday, October 12, 2012 8:21 PM