CODEWITHSUNDEEP
vbaexcelworksheet-function
prerna arora
prerna arora

Reputation: 85

how to use countif function having the criteria as a part of string literal?

For example i want to count the number of "johns" appearing in the column A however column has both first name and last name and i want to count all the johns irrespective of their last name.

I am using the below code however doesn't seem to work:

Range("A199").Value = WorksheetFunction.CountIf(Range("A1", Range("A1").End(xlDown)), Cells.Find(What:="John "))

Upvotes: 2

Views: 1883

Answers (3)

Shai Rado
Shai Rado

Reputation: 33682

As @YowE3K commented, there is a reason why I use "=" & "John*"). It's a preparation for using a variable in the future instead of the hard-coded "John".

If you want to use a VBA solution , you can use:

Range("A199").Value = WorksheetFunction.CountIf(Range("A1", Range("A1").End(xlDown)), "=" & "John*")

Or, better yet:

Const NametoFind As String = "John"

Range("A199").Value = WorksheetFunction.CountIf(Range("A1", Range("A1").End(xlDown)), "=" & NametoFind & "*")

Note: Range("A1", Range("A1").End(xlDown)) will work for continous range only (not if you have blank cells in the middle of you range).

Upvotes: 1

Jakob Busk Sørensen
Jakob Busk Sørensen

Reputation: 6081

If you are allowed to use an extra column, make one right after you name column, using the following formula:

=ISNUMBER(SEARCH("JOHN"; A1)

The above example should be in cell B1. Then you will get a TRUE if the cell in column A contains the name "JOHN", otherwise you will get a FALSE. Then you simply make a count:

=COUNTIF(B:B; TRUE)

It might be a slight disadvantage that you have to use an extra column. But on the plus-side, it is very understandable (both for yourself and others whom might read your code).

Upvotes: 0

Josh Friedlander
Josh Friedlander

Reputation: 11657

You don't need VBA. You can use a wildcard with COUNTIF/COUNTIFS - make your criteria "John*" (so, =COUNTIF(A1:A7, "John*")).

Upvotes: 3

Related Questions