COUNTIF function with date criteria

[elliot01]

Hi all,

A bit rusty with Excel formulas.

I'm trying to get the following to work:

=COUNTIF(Sheet1!C:C,MONTH(C:C)=3)

(I want to count the rows applicable to March (i.e.: month 3)).

Column C on 'Sheet1' contains data such as:

<TABLE style="WIDTH: 70pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=93><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3968" width=93><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 70pt; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl64 height=15 width=93>25/03/2010 12:23</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64 height=15>23/03/2010 21:00</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl64 height=15>23/03/2010 21:05</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64 height=15>25/03/2010 19:13</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl64 height=15>25/03/2010 19:58</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64 height=15>25/03/2010 20:03</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl64 height=15>26/03/2010 09:18</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64 height=15>26/03/2010 10:55</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" class=xl64 height=15>26/03/2010 11:05</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 8pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl64 height=15>26/03/2010 11:20</TD></TR></TBODY></TABLE>

Unfortunately, Excel is not having any of it and always produces a zero count.

Can anyone point out where I am going wrong please?

Thanks in advance.

Elliot

 

[MrKowz]

Welcome to the forums!

You'll actually need to use a SUMPRODUCT for this, as it has to perform a secondary evaluation for each entry:

=SUMPRODUCT(--(MONTH(C1:C100)=3))

 

[elliot01]

That's mint!

Worked a treat Thank you