sumproduct and date criteria

[cswildrick]

I am trying to create a sumproduct formula that will count the number of filled positions during the month of July 2011 and return the value. I am having trouble with how the date should be formulated. Can you help?

<TABLE style="WIDTH: 164pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=218 x:str><COLGROUP><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1536" width=48><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3136" width=98><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl26 height=20 width=48>Status</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl26 width=72>Date</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl26 width=98>New Hire</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=19 width=48>F</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 width=72 x:num="40701" u1:num="40701">6/7/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=98>Alicia Rose</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=19 width=48>F</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 width=72 x:num="40721" u1:num="40721">6/27/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=98>Sarah Huff</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=19 width=48>O</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=72 u1:num="40721"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=98>Tom Cruz</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=19 width=48>F</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 width=72 x:num="40710" u1:num="40710">6/16/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=98>Doug Rainman</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=19 width=48>F</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 width=72 x:num="40735" u1:num="40735">7/11/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=98>Nathan Kline</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=19 width=48>O</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=72 u1:num="40749"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=98>Rachel Ray</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=19 width=48>F</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 width=72 x:num="40742" u1:num="40742">7/18/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=98>Robert Redford</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=19 width=48>F</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 width=72 x:num="40611" u1:num="40611">3/9/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=98>Billy Bob</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=19 width=48>F</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 width=72 x:num="40735" u1:num="40735">7/11/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl25 width=98>Willy Wonka</TD></TR></TBODY></TABLE>

 

[p45cal]

something on these lines?:
=SUMPRODUCT((F39:F47="F")*(MONTH(G39:G47)=7)*(YEAR(G39:G47)=2011))

 

[cswildrick]

The solution works great!! Thanks for taking the time to provide a solution. I appreciate it greatly.

Chris

 

[T. Valko]

I am trying to create a sumproduct formula that will count the number of filled positions during the month of July 2011 and return the value. I am having trouble with how the date should be formulated. Can you help?

<TABLE style="WIDTH: 164pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=218 border=0 x:str><COLGROUP><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1536" width=48><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3136" width=98><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 36pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=48 height=20>Status</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72>Date</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>New Hire</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 36pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=48 height=19>F</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72 x:num="40701" u1:num="40701">6/7/2011</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>Alicia Rose</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 36pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=48 height=19>F</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72 x:num="40721" u1:num="40721">6/27/2011</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>Sarah Huff</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 36pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=48 height=19>O</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72 u1:num="40721"></TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>Tom Cruz</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 36pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=48 height=19>F</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72 x:num="40710" u1:num="40710">6/16/2011</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>Doug Rainman</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 36pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=48 height=19>F</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72 x:num="40735" u1:num="40735">7/11/2011</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>Nathan Kline</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 36pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=48 height=19>O</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72 u1:num="40749"></TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>Rachel Ray</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 36pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=48 height=19>F</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72 x:num="40742" u1:num="40742">7/18/2011</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>Robert Redford</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 36pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=48 height=19>F</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72 x:num="40611" u1:num="40611">3/9/2011</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>Billy Bob</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 36pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=48 height=19>F</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72 x:num="40735" u1:num="40735">7/11/2011</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>Willy Wonka</TD></TR></TBODY></TABLE>

Here's another one...

=SUMPRODUCT(--(A2:A10="F"),--(TEXT(B2:B10,"mmmyyyy")="Jul2011"))