Thursday, December 16, 2004

Using COUNTIF in Excel

COUNTIF is the function to use when you want to count the number of instances a certain text or value appears.
Both of the two examples below only count entire cells. Counting part cells is more complicated and will be discussed separately.

Spreadsheet Example
In a range of data you have something for which you want to count the number of instances. Eg a range of names in cells A1:A50 and you want to see how many times bob's name appears.
the formula is =COUNTIF(A1:A50,"bob") Note that "bob" is case sensitive. and the actual string you are searching for is inside the quotes. To search for a value (eg 15) use =COUNTIF(A1:A50,15). No quotes this time as you are not looking for a text string.
To find multiple strings or values in the same range use this extended formula =COUNTIF(A1:A50,"bob")+COUNTIF(A1:A50,15) to count the number of times bob and 15 appear.

VBA example using a User Defined Function and excludes hidden rows (eg in a filter)

Public Function MyCount(Rng As Range, CountValue)
Dim Sel As Range
MyCount = 0
For Each Sel In Intersect(Rng, Rng.Parent.UsedRange)
If Sel.EntireRow.Hidden = True Then
Else
If Sel.Value = CountValue Then
MyCount = MyCount + 1
End If
End If
Next
End Function

Copy the above text into a module in your workbook (press ALT F11 and then insert a module and paste the text in.)

You can then use the following formula =MyCount(A1:A50,"bob")

If you want to learn how to do multiple criteria counts then look for an article soon on CSE (CTRL SHIFT ENTER) or array formulas or email me with a request.

I am slowly processing the requests for tips I have been sent, so if you haven't seen yours answered yet please be patient. Feel free to send in more requests.


Posted by: Jethro | 10:42 AM |