Results 1 to 9 of 9

Thread: Counting ""

  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    493
    Thanks
    82
    Thanked 2 Times in 1 Post

    Counting ""

    Hi Lounger, I need some help with this.

    I have this simple formula in a column S that extends down a 100 or so rows. =IF(U$6="","",IF($S7="no","N/A",""))

    I'm trying to count the number of "" (appearing empty) in column U. Although the range extends down 100 or so rows there are only values in column "S" in the first 20 cells (at this stage) of which only 2 cells contain "".

    I'm trying to create a formula that would return a total of 2.

    I hope that makes sense

    Any thoughts/suggestions would be much appreciated.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,953
    Thanks
    61
    Thanked 1,104 Times in 1,027 Posts
    Have you tried the COUNTBLANK function?

    cheers, Paul

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    493
    Thanks
    82
    Thanked 2 Times in 1 Post
    Hi Paul - yes tried that but no luck

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,396
    Thanks
    164
    Thanked 634 Times in 602 Posts
    Hi verada

    Your formula in column [S]
    =IF(U$6="","",IF($S7="no","N/A",""))
    ..are you sure this is the formula you have?????

    zeddy

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,919
    Thanks
    152
    Thanked 746 Times in 678 Posts
    Verada,

    Your formulas in Column S are ambiguous in that they will always return "" because they depend on values in the S column below it to be equal to "no". This will not occur unless you physically overwrite the formula in column S with "no" or type "no" in the last row. Could this be what you are looking for? In cell S6 place this formula and copy down:

    =IF(U6<>"","",COUNTIF($U$6:$U6,""))

    Count.png

    HTH,
    Maud

  6. #6
    New Lounger
    Join Date
    Feb 2015
    Location
    Derby
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Could the ISBLANK function be used?

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,953
    Thanks
    61
    Thanked 1,104 Times in 1,027 Posts
    If COUNTBLANK doesn't work I can't see ISBLANK working.

    cheers, Paul

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,919
    Thanks
    152
    Thanked 746 Times in 678 Posts
    CountBlank would certainly work. But Verada is attempting to find a formula in Col S that he copies down to do so. I am guessing that he wants to progressively count them. In cell S6 and copy down, this would work equally as well.

    =IF(U6<>"","",COUNTBLANK($U$6:$U6))
    Last edited by Maudibe; 2015-02-21 at 09:16.

  9. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    493
    Thanks
    82
    Thanked 2 Times in 1 Post
    Hi All - thanks for your help and thoughts.

    My idea was that if the cell was "" based on the "if" criteria that would prompt a value to be entered (by a data entry person) that would over-right the formula. I might have a re-think about the best was to do this.

    I'll try all your suggestions.

    Thanks again

    Regards

Posting Permissions

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