Results 1 to 6 of 6

Thread: Count if or VBA or Code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2019
    Posts
    196
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Count if or VBA or Code

    Hi All,

    Thanks in advance for your help.

    we have the following sheet, its a sample sheet (have data approx 90 row and 100 column)

    Excel 2010 32 bit
    A
    B
    C
    D
    E
    F
    1
    Marks
    Ram
    Ravi
    Rozi
    Sunny
    Ahmed
    2
    45
    Na
    Na
    Na
    Na
    Na
    3
    65
    Na
    Na
    Na
    Na
    Na
    4
    25
    Yes
    Yes
    Yes
    Yes
    Yes
    5
    35
    Na
    Na
    Na
    Na
    Na
    6
    26
    Yes
    Yes
    Yes
    Yes
    Na
    7
    98
    Na
    Na
    Na
    Na
    Na
    8
    78
    Na
    yes
    Na
    Na
    Na
    9
    15
    Na
    Na
    yes
    Na
    Na
    10
    45
    Na
    Na
    Na
    yes
    Na
    Sheet: Sheet1



    Need the following result

    Excel 2010 32 bit
    A
    B
    C
    D
    E
    F
    1
    Marks
    Ram
    Ravi
    Rozi
    Sunny
    Ahmed
    2
    45
    Na
    Na
    Na
    Na
    Na
    3
    65
    Na
    Na
    Na
    Na
    Na
    4
    25
    Yes
    Yes
    Yes
    Yes
    Yes
    5
    35
    Na
    Na
    Na
    Na
    Na
    6
    26
    Yes
    Yes
    Yes
    Yes
    Na
    7
    98
    Na
    Na
    Na
    Na
    Na
    8
    78
    Na
    yes
    Na
    Na
    Na
    9
    15
    Na
    Na
    yes
    Na
    Na
    10
    45
    Na
    Na
    Na
    yes
    Na
    11
    12
    Total "Na" in continuation from bottom
    4
    2
    1
    6
    13
    14
    Sheet: Sheet1

    We have counted Total "Na" which are in continuation from bottom. After this we want to compare

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,247
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Count if or VBA or Code

    Here is a UDF (user defined function) that you can use...
    Code:
    Function CountNA(Rng As Range) As Variant
      Dim Arr As Variant
      Arr = Split(Join(Application.Transpose(Rng), ""), "yes", , vbTextCompare)
      CountNA = Len(Arr(UBound(Arr))) / 2
      If CountNA = 0 Then CountNA = ""
    End Function
    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CountNA just like it was a built-in Excel function. For example,

    =CountNA(B2:B10)

    If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular
    Join Date
    Mar 2019
    Posts
    196
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count if or VBA or Code

    Thanks for the help Rick Rothstein Sir Ji,

    i have used the formula but its not giving accurate result, check in below sheet

    Excel 2010 32 bit
    A
    B
    C
    D
    E
    F
    1
    First Data
    2
    Marks
    Ram
    Ravi
    Rozi
    Sunny
    Ahmed
    3
    45
    Na
    Na
    Na
    Na
    Na
    4
    65
    Na
    Na
    Na
    Na
    Na
    5
    25
    Yes
    Yes
    Yes
    Yes
    Yes
    6
    35
    Na
    Na
    Na
    Na
    Na
    7
    26
    Yes
    Yes
    Yes
    Yes
    Na
    8
    98
    Na
    Na
    Na
    Na
    Na
    9
    78
    Na
    yes
    Na
    Na
    Na
    10
    15
    Na
    Na
    yes
    Na
    Na
    11
    45
    Na
    Na
    Na
    yes
    Na
    12
    13
    Total "Na" in continuation from bottom
    4
    2
    1
    6
    14
    15
    16
    17
    with your instructions
    3
    1
    3
    5
    Sheet: Sheet1

    Help pls

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,247
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Count if or VBA or Code

    When my example showed this formula to use...

    =CountNA(B2:B10)

    the range was based on the data you posted in Message #1 ... the data you posted in Message #3 has everything one row lower. So the data you want to count is no longer in the range B2:B10 for the first column... you moved it to B3:B11 so the formula you must use is this...

    =CountNA(B3:B10)

    The formula cannot figure out on its own where your data is... you must tell it. So, if you add more rows of data, you will have to adjust the range given to the UDF so it knows where the data it has to parse is located at.
    Last edited by Rick Rothstein; Sep 17th, 2019 at 03:39 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular
    Join Date
    Mar 2019
    Posts
    196
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count if or VBA or Code

    Thanks for the help Rick Rothstein Sir Ji,

    I am really sorry for not read carefully, really sorry

    its working

    Thanks

  6. #6
    Board Regular
    Join Date
    Mar 2019
    Posts
    196
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count if or VBA or Code

    i am really sorry

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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