How to count instances meeting criteria?

**guntar** · 04-26-2009, 03:39 PM

Gentlemen,
There is something I want to count in my data. Please look at the file I attached to see my challenge.
Thank you in advance

**Stuie** · 04-26-2009, 03:46 PM

Hi guntar,

Are you looking to use VBA to do this? if so you can loop the cells and count howmany have X in them like so

Sub GetWstCount()
Dim c As Range
Dim counter As Integer

For Each c In Range("G7:G16")
    If c.Value = "X" Then
        counter = counter + 1
    End If
Next c

MsgBox "There were " & counter & "!"
End Sub

**guntar** · 04-26-2009, 03:49 PM

No, only with functions available in Excel

**VBA Noob** · 04-26-2009, 04:03 PM

Originally Posted by guntar

No, only with functions available in Excel

So why post in programming then?

Thread moved to worksheet functions as per forum rules below

VBA Noob

**martindwilson** · 04-26-2009, 04:00 PM

=countif(k7:k16,"x")

**guntar** · 04-26-2009, 04:12 PM

Thank you martindwilson, it works if I would have only one column, but I have more than one “WEST” column. I need something what looks in a range E7:L7 first and then goes down 7:16 rows searching for matching criteria.
Thank you

**martindwilson** · 04-26-2009, 04:50 PM

how many? are they always nswenswenswe and so?
why not just total all the count ifs?
use a few helper cells you could always put them on another sheet if you dont want to see them

**guntar** · 04-26-2009, 05:01 PM

I want to count how many names have been in WEST, there will be more West columns added later, and I want that information to be updated automatically, the end result will feed the Chart.
Thank you

**martindwilson** · 04-26-2009, 05:06 PM

see previous post it has an idea you can extend the ranges if needed
edit did you say names? if name 1 is in west 10 times did you want 1 or 10 returned?