Results 1 to 9 of 9

Thread: Counting ""

  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    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,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    Have you tried the COUNTBLANK function?

    cheers, Paul

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    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
    2,826
    Thanks
    136
    Thanked 482 Times in 459 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,637
    Thanks
    115
    Thanked 650 Times in 592 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,191
    Thanks
    48
    Thanked 985 Times in 915 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,637
    Thanks
    115
    Thanked 650 Times in 592 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
    487
    Thanks
    75
    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
  •