Counting instances of specific value

atsukoarai86 · #1 (**permalink**) 12-07-09, 14:01

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.

Colin Legg · #2 (**permalink**) 12-07-09, 14:24

Hi,

Quote:

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?

atsukoarai86 · #3 (**permalink**) 12-07-09, 15:37

Yes! Yes that helps much and perfectly! Thank you for your response. I had a feeling it was going to be something really simple.

Colin Legg · #4 (**permalink**) 12-07-09, 18:18

You're welcome.

Internet Services

Web Development

Digital Marketing

Consumer Tech