Count blank cells

[JackDanIce]

Hi,

Have the following formula which counts the number of cells within a list based on a start date and end date:

=COUNTIF(Monthly!$I$3:$I$479,"<="&$B$3)-COUNTIF(Monthly!$I$3:$I4$79,"<"&$B$2)

How do I count just the blank cells?

$B$2 = Start date (user input)
$B$3 = End date (user input)
Monthly!$I$3:$I$479 = Range to evaluate user inputs against (ordered ascending)

Thanks,
Jack

 

[Jonmo1]

Try reversing the logic, use > instead of <

=COUNTIF(Monthly!$I$3:$I$479,">="&$B$2)-COUNTIF(Monthly!$I$3:$I4$79,">"&$B$3)

 

[Jonmo1]

Maybe I missunderstood...

Perhaps

=COUNTBLANK(Monthly!$I$3:$I4$79)

 

[JackDanIce]

Hi Jonmo,

Nearly there with your second suggestion. As example, say I have the dates 1st March 2011 and 31st March 2011 on my Monthly sheet, I need the formula to count all the cells between those two dates that are blank (i.e. number of blank cells in March)

However, range I3:I479 (typo below of $I4$79) starts on 1st Jan 2011 and will continue indefinite (or when they leave me alone and ask for a new spreadsheet), so your suggestion would count the total number of blank cells and not be limited as required..

Thanks,
Jack

 

[Jonmo1]

OK, I see.

Try

=COUNTBLANK(INDEX(I3:I479,MATCH(D1,I3:I479,0)):INDEX(I3:I479,MATCH(E1,I3:I479,0)))

D1 = Start Date (March 1st)
E1 = End Date (March 31st)

 

[JackDanIce]

Not working but realised, the dates are not ordered (nor can they be). However, if I change the '0' in the MATCH condition to '1' it works with this example, so guess this is using a nearest match approach.

I'm going to test this for a bit for February and January, but I think it's almost there.

Thank you jonmo, I appreciate your input.
Enjoy the rest of your day!
Jack

 

[JackDanIce]

Shame, February only has two blank cells, but formula gives value of 4, I think the '1' part is messing up but not sure how to change; using '0' brings back a value of '#N/A'

 

[Jonmo1]

the dates are not ordered (nor can they be).

Can you clarify that?

Do you mean the dates are not in chronological order from earliest to latest?
Also, are not ALL dates present?

 

[JackDanIce]

Hi jonmo,

The dates are mostly in chronological order, by that I mean, the months are always in chronological order, but the days (within each month) are not. E.g. for March, the dates might be:
3, 3, 7, 12, 7, 14, 18, 15, 20, 25, 30, 27

Then April will be created but again, the dates within April may not necessarily be in chronological order

And yes, there may be missing dates (hence to count the blanks)

I will post an example of the sheet tomorrow when I am at work, an example will probably be better than my descriptions.

Thanks.
Kunj

 

[JackDanIce]

Morning jonmo,

Partial example of data is (too big to paste the whole thing in):

Monthly

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 75px"><COL style="WIDTH: 98px"><COL style="WIDTH: 97px"><COL style="WIDTH: 100px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">Deadline</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">Responsible</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">Sign-Off</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">Delivery Date</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">63</TD><TD style="TEXT-ALIGN: right">16/03/2011</TD><TD>B</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">15/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">64</TD><TD style="TEXT-ALIGN: right">16/03/2011</TD><TD>C</TD><TD>C</TD><TD style="TEXT-ALIGN: right">10/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">65</TD><TD style="TEXT-ALIGN: right">07/03/2011</TD><TD>B</TD><TD>D</TD><TD style="TEXT-ALIGN: right">10/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">66</TD><TD style="TEXT-ALIGN: right">07/03/2011</TD><TD>A</TD><TD>A</TD><TD style="TEXT-ALIGN: right">04/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">67</TD><TD style="TEXT-ALIGN: right">02/03/2011</TD><TD>A</TD><TD>A</TD><TD style="TEXT-ALIGN: right">02/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">68</TD><TD style="TEXT-ALIGN: right">03/03/2011</TD><TD>A</TD><TD>A</TD><TD style="TEXT-ALIGN: right">03/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">69</TD><TD style="TEXT-ALIGN: right">14/03/2011</TD><TD>A</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">70</TD><TD style="TEXT-ALIGN: right">14/03/2011</TD><TD>A</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">71</TD><TD style="TEXT-ALIGN: right">21/03/2011</TD><TD>B</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">21/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">72</TD><TD style="TEXT-ALIGN: right">23/03/2011</TD><TD>A</TD><TD>A</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">23/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">73</TD><TD style="TEXT-ALIGN: right">21/03/2011</TD><TD>B</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">21/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">74</TD><TD style="TEXT-ALIGN: right">21/03/2011</TD><TD>B</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">21/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">75</TD><TD style="TEXT-ALIGN: right">23/03/2011</TD><TD>A</TD><TD>A</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">23/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">76</TD><TD style="TEXT-ALIGN: right">21/03/2011</TD><TD>B</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">18/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">77</TD><TD style="TEXT-ALIGN: right">23/03/2011</TD><TD>A</TD><TD>A</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">23/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">78</TD><TD style="TEXT-ALIGN: right">21/03/2011</TD><TD>B</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">21/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">79</TD><TD style="TEXT-ALIGN: right">21/03/2011</TD><TD>B</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">21/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">80</TD><TD style="TEXT-ALIGN: right">23/03/2011</TD><TD>A</TD><TD>A</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">23/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">81</TD><TD style="TEXT-ALIGN: right">24/03/2011</TD><TD>B</TD><TD>B</TD><TD style="TEXT-ALIGN: right">21/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">82</TD><TD style="TEXT-ALIGN: right">07/03/2011</TD><TD>B</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">07/03/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">83</TD><TD style="TEXT-ALIGN: right">07/03/2011</TD><TD>B</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">07/03/2011</TD></TR></TBODY></TABLE>

So here:

- Column I contains 21 rows of (filtered) data for March only
- Column L contains 19 non-blank cells.
- There is no other data for March outside of this range

- Formula in my inital post counts number of non-blank cells in column I that lie between the provided date range (in this case March)

- I'd like a formula that counts the number of blanks (or non) blanks in column L, relative to the provided date range

Hope this provides more clarity, let me know if you need anything more.

Thanks,
Jack