Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Question Unanswered: Counting instances of specific value

    Hi there. I'm pretty adept w/ VBA in MS Access, but I'm a total n00b at using it in Excel. Never ventured in to Excel VBA, so I don't really...get it. I've been scouring the web looking for a solution here for a couple hours now, so I figured I'd post here and see if the most brilliant minds of our time could help me out.

    I've got a very simple task. I've got a range of say H10:H39. Each of these cells is going to either say Y, N, or NA - I want to just ... have a cell beneath the column that sums up the number of Ys in the range. That is all.

    Like I said, total n00b here, I appreciate the help.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    I've got a very simple task. I've got a range of say H10:H39. Each of these cells is going to either say Y, N, or NA - I want to just ... have a cell beneath the column that sums up the number of Ys in the range. That is all.
    This can be done with a formula, so there's probably no need to use Excel VBA. It's really a count, not a sum, and there is one condition so the COUNTIF() worksheet function is suitable.

    Say your total is in the cell H40, put this formula in that cell:
    =COUNTIF(H10:H39,"y")

    The "y" string condition is not case sensitive.


    If this is part of a process you are automating, then you can put the formula into the cell using Excel VBA by using the Range.Formula property. An example would be:
    Code:
    Sub foo()
    
        Worksheets("Sheet1").Range("H40").Formula = "=COUNTIF(H10:H39,""y"")"
        
    End Sub

    Hope that helps?
    Last edited by Colin Legg; 12-07-09 at 14:29.

  3. #3
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Talking

    Yes! Yes that helps much and perfectly! Thank you for your response. I had a feeling it was going to be something really simple.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    You're welcome.

Posting Permissions

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