# Thread: Counting instances of specific value

1. Registered User
Join Date
Sep 2008
Location
NM, USA
Posts
97

## 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.

2. Registered User
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. Registered User
Join Date
Sep 2008
Location
NM, USA
Posts
97
Yes! Yes that helps much and perfectly! Thank you for your response. I had a feeling it was going to be something really simple.

4. Registered User
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
•