CountIfs : Adding Empty Cell Values

[williamx]

Hey Guys

I'm attempting to use countifs to count empty cells is this possible?

I have 3 columns

(A) (B) (C)
Date Order ID Shipped

What I'm trying to calculate is the amount of orders that are awaiting shipment based on the original order date.

The Data would look like this


(A) (B) (C)
Date Order ID Shipped
05/19/11 123ABC qwe123
05/20/11 456ABC
05/20/11 789ABC asd123
05/20/11 123DEF zxc987

How can i calculate how many empty values are in C that have data in Cell B and relate back to the 20th of May?

I've used "<>" to indicate that there is something in the cell however cant seem to figure out what to do when the cell is empty.

Thanks for your help

 

[MrKowz]

Try:

=COUNTIF(C:C,"=")

 

[williamx]

thanks for your quick reply,

that does work to count the empty cells in C.

However I can only count those empty values in C if there is a value present in B

I'm trying

=COUNTIFS(C:C,"=",B:B,"<>")

however it keeps returning 0

any ideas?

Thanks

 

[MrKowz]

Strange, it works fine for me:

Excel 2007
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Date</TD><TD>OrderID</TD><TD>Shipped</TD><TD style="TEXT-ALIGN: right"></TD><TD>Count:</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">5/19/2011</TD><TD>123ABC</TD><TD>qwe123</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">5/20/2011</TD><TD>456ABC</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">5/20/2011</TD><TD>789ABC</TD><TD>asd123</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">5/20/2011</TD><TD>123DEF</TD><TD>zxc987</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>



<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F1</TH><TD style="TEXT-ALIGN: left">=COUNTIFS(C:C,"=",B:B,"<>")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

See if this alternative gives you the desired value:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style=";">OrderID</td><td style=";">Shipped</td><td style="text-align: right;;"></td><td style=";">Count:</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">5/19/2011</td><td style=";">123ABC</td><td style=";">qwe123</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5/20/2011</td><td style=";">456ABC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">5/20/2011</td><td style=";">789ABC</td><td style=";">asd123</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">5/20/2011</td><td style=";">123DEF</td><td style=";">zxc987</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">B2:B5<>""</font>),--(<font color="Red">C2:C5=""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[williamx]

Hey

Should of maybe mentioned I'm using the Mac version of 2011.

The Sum Product worked, now how would I add the date look up to tell how many empty cells are in C for the 19th of may and how many empty cells are in C for the 20th?

thanks again for your help.

 

[MrKowz]

Try:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #FFFFFF;;">Date</td><td style="background-color: #FFFFFF;;">OrderID</td><td style="background-color: #FFFFFF;;">Shipped</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;">5/19/2011</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #FFFFFF;;">5/19/2011</td><td style="background-color: #FFFFFF;;">123ABC</td><td style="background-color: #FFFFFF;;">qwe123</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;">5/20/2011</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;background-color: #FFFFFF;;">5/20/2011</td><td style="background-color: #FFFFFF;;">456ABC</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;background-color: #FFFFFF;;">5/20/2011</td><td style="background-color: #FFFFFF;;">789ABC</td><td style="background-color: #FFFFFF;;">asd123</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">5/20/2011</td><td style="border-bottom: 1px solid black;background-color: #FFFFFF;;">123DEF</td><td style="border-bottom: 1px solid black;background-color: #FFFFFF;;">zxc987</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$5=E1</font>),--(<font color="Red">$B$2:$B$5<>""</font>),--(<font color="Red">$C$2:$C$5=""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />