Ayuda Contar Si (dos criterios)

JavierPeña

New Member
Joined
Apr 21, 2011
Messages
37
Muy buenas,

Tengo un problema con la fucnión Contar.Si, para dos criterios.

La cosa es que tengo dos columnas:

Columna A (Formato fecha)|Columna B (Formato texto)
01/01/2002|Si
02/05/2002|No
02/05/2002|No
02/05/2002|Si
02/05/2003|No
02/05/2003|Si
02/05/2003|No
02/05/2003|Si


Ahora me gustaría mostar, en otra celda, el número de filas del año 2002 que además son 'Si'.

Cómo podría hacerlo?

Un saludo y muchas gracias!
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

=SUMAPRODUCTO(--(AÑO(A1:A8)=2002);--(B1:B8="Si"))
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
This is my result using the English version of Excel. I cannot test the Spanish version

Excel Workbook
ABC
101/01/2002Si2
202/05/2002No
302/05/2002No
402/05/2002Si
502/05/2003No
602/05/2003Si
702/05/2003No
802/05/2003Si
Sheet4



Perhaps you should ask your question in the Questions in other languages section.
 

JavierPeña

New Member
Joined
Apr 21, 2011
Messages
37

ADVERTISEMENT

Maybe the error appear because i'm trying to do this in other sheet?

What do u think?

anyway i'm going to ask in spanish Post, right?

Best regards,
 

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
Are you ****ing joking?

This website is insane.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

anyway i'm going to ask in spanish Post, right?
It should only be asked in one place.

I am therefore moving this thread to the Other Languages forum and will delete your duplicate post from there.
 
Last edited:

cgcamal

Active Member
Joined
May 2, 2007
Messages
472
Hola Javier,

Las funciones no encuentran la cadena 2002, porque Excel las fechas las muestra en formato de lectura humana, pero internamente es un entero,podés apreciarlo si cambias el formato de fecha a general, la primera cambiara a 37257.

La siguiente fórmula funciona si anteponés un apóstrofe (') a la fecha para hacer que Excel lo entienda como cadena de texto, o sea, en vez de 01/01/2002, introducir '01/01/2002.
Code:
=CONTAR.SI.CONJUNTO(A1:A8,"*2002*",C1:C8,"Si")
Para mejorar eso sin usar el apostrofe tendremos que pensar en algo distinto.

Saludos cordiales
 

JavierPeña

New Member
Joined
Apr 21, 2011
Messages
37
Muchas gracias,

Pues la verdad es que es una p...ada.

Lo cierto es que si pongo el apóstrofe pierde bastante funcionalidad, porque son más de 3000 registros con fechas y ampliables.

Cómo puedo hacer para que excel sepa el año que es?

Saludos,
 

cgcamal

Active Member
Joined
May 2, 2007
Messages
472
Hola Javier,

Una opción podría ser usando CONTAR.SI.CONJUNTO y definiendo un rango de criterios como abajo:

Excel Workbook
ABCDE
101/01/2002SiCuentaRango criterios
202/05/2002No201/01/200231/12/2002
302/05/2002No
402/05/2002Si
502/05/2003No
602/05/2003Si
702/05/2003No
802/05/2003Si
Hoja1
Cell Formulas
RangeFormula
C2=COUNTIFS(A1:A8,">="&D2,A1:A8,"<="&E2,B1:B8,"Si")


Espero ayude.

Saludos cordiales
 

Watch MrExcel Video

Forum statistics

Threads
1,122,295
Messages
5,595,294
Members
413,984
Latest member
stikpet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top