countifs with 2/3 criteria. blanks, and a given month

[chrisdvs]

I am trying to create a formula that considers 2 columns (values and dates). These columns may grow so I use as range A:A and B:B.
I want to count the blanks for a given month.

=COUNTIFS('Sheet2'!$A:$A,isblank(),Sheet2'!$B:$B,">=01-01",'Sheet2'!$B:$B,"<02-01")

But that does not work. Any hints?

 

[T. Valko]

I am trying to create a formula that considers 2 columns (values and dates). These columns may grow so I use as range A:A and B:B.
I want to count the blanks for a given month.

=COUNTIFS('Sheet2'!$A:$A,isblank(),Sheet2'!$B:$B,">=01-01",'Sheet2'!$B:$B,"<02-01")

But that does not work. Any hints?

Try it like this...


Use cells to hold the date criteria:

• A2 = lower date boundary = 1/1/2011
• B2 = upper date boundary = 1/31/2011

Then:

=COUNTIFS(Sheet2!A:A,"",Sheet2!B:B,">="&A2,Sheet2!B:B,"<="&B2)

 

[Nalani]

deleted

 

[chrisdvs]

Almost!

From your table, lets change row 6 to say July (7/19/2011) in column B
So I want the blanks from June only. Result should be 1 in that case.

 

[Nalani]

Sorry I deleted my post because I had read it wrong, and Biff had it right.

Here is the Table with Biff's formula, since you seem to be referencing it.



<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 68px"><COL style="WIDTH: 32px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">6/1/2011</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">6/30/2011</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">6/14/2011</TD><TD></TD><TD style="FONT-WEIGHT: bold">Blanks</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">6/15/2011</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">6/16/2011</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD style="TEXT-ALIGN: right">6/17/2011</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">6/18/2011</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD style="TEXT-ALIGN: right">7/19/2011</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">6/20/2011</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D4</TD><TD>=COUNTIFS(Sheet2!A:A,"",Sheet2!B:B,">="&A2,Sheet2!B:B,"<="&B2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[chrisdvs]

Thanks a lot! I have been looking for that for a while.