Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Steve,

    That seems to work! Thank you much!

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •