Thread: Count blanks in a list (Excel 2000/SP-3)

1. Count blanks in a list (Excel 2000/SP-3)

Gurus,

I have a list that diminishes every few minutes. When I have completed processing a line, I write "done" in column A for that line. Every so often, I sort my "done"'s to the bottom, and restart at the top. Please see my attachment.

I'm trying to count the number of undone rows in this list. A row is undone if it doesn't contain "done" in A. My first thought was COUNTBLANK, as that would give me the number of undones, but that's not quite accurate. Once the list diminishes a bit, I'm now counting blank cells outside of my list. I have to continuously adjust my range.

I want a formula that will count the number of blanks in A, regardless of how the range may change.

Incidentally, my users don't even know what VB stands for, they need a formula I can explain to them. Formula's only please.

As always, you folks are wonderful!

2. Re: Count blanks in a list (Excel 2000/SP-3)

If B will always be full for the range how about something like:
<pre>=COUNTA(B:[img]/forums/images/smilies/cool.gif[/img]-COUNTA(A:A)-1</pre>

It counts all in B subtracts the number in A and subtracts 1 (for the header in [img]/forums/images/smilies/cool.gif[/img]. This is the blanks. (you could use another column if desired)

If you put something in A1 you could use:
<pre>=COUNTA(B:[img]/forums/images/smilies/cool.gif[/img]-COUNTA(A:A)</pre>

Steve

3. Re: Count blanks in a list (Excel 2000/SP-3)

Steve,

That seems to work! Thank you much!

4. Re: Count blanks in a list (Excel 2000/SP-3)

An alternative solution would be using "Array Formulas" such as the one depicted below. With minor tweaking you should be able to modify the array formula for just blank cells.

Regards,
John

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•