Excel COUNTIF with strings, separated by comma

Question

I am trying to write a simple formula to count how many times a particular name appears in a column. I am using COUNTIF as it is a pretty straight forward process but I cannot work out how to make it happen for a name in particular. This is the case:

enter image description here

The column named Age will display cells with one or more names, separated by commas in case there are more than one value. Putting "Young" as an example is easy to tell the COUNTIF formula to give me the number representing how many times this word appears, either being the only value in cell or as a part of a cell with a longer string value by giving the formula the "Young" statement.

The problem comes when I want the formula to count how many times "Mature" appears in my column. I cannot work out the way to make it count only when it says "Mature" without also taking all the "Early_Mature" or "Semi_Mature"

I know this is easy for whoever knows the basics of Excel so I don't think there is need to give more details.

Thanks

Hakan ERDOGAN · Accepted Answer · 2018-05-31 08:58:19Z

1

Most of the times I succeed solving such problems by adding the same delimiter (of our string) at the beginning and end of the main string.

So since your data is at COL:Y, you may create a new helper COL:Z and enter this formula:

="," & Y1 & ","

I did not use any spaces before or after comma since your data seems not having any space. Depending on your case, you may have to use spaces.

Now your string is wrapped with commas, which you may alter COUNTIF formula to such:

=COUNTIF(Z:Z,"*,"&B1&",*")

* characters are jokers which stand for "anything" in this context.

answered May 31, 2018 at 8:58

Hakan ERDOGAN

1,2209 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

QHarr · Accepted Answer · 2018-05-31 09:18:57Z

With an UDF. Code goes in a standard module added by opening the VBE with Alt + F11 then right-click in project explorer and add module.

Code

Option Explicit

Public Function GetCount(ByRef selectRange As Range, ByVal searchTerm As String) As Long
    Application.Volatile
    With selectRange

        Dim arr(), joinedString As String, i As Long, outputCount As Long
        arr = .Value

        joinedString = Join(Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Index(arr, 0, 1)), ",")

        Dim arr2() As String
        arr2 = Split(joinedString, ",")

        For i = LBound(arr2) To UBound(arr2)
            If Trim$(arr2(i)) = "Mature" Then
                outputCount = outputCount + 1
            End If
        Next i
    End With

    GetCount = outputCount

End Function

Usage in sheet

zipa · Accepted Answer · 2018-05-31 09:05:20Z

0

To get the number of occurrences of Mature excluding those that have prefix you can use this array formula:

=SUM(((LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"Mature",""))) / LEN("Mature"))-((LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"_Mature",""))) / LEN("_Mature")))

Please take note that this formula is applied with Ctrl + Shift + Enter.

Given that your range is in Y:Y column, just change the range to one you need.

answered May 31, 2018 at 9:05

zipa

28k6 gold badges45 silver badges62 bronze badges

1 Comment

G.Spanish Over a year ago

Thanks to all for the answers, unfortunately It has to be without creating extra columns and, although I am a big fan of VBA found this one quite useful. Thanks mate ;)

MikeK · Accepted Answer · 2018-05-31 14:52:18Z

0

An alternative would be to change "Mature" to "Fully_Mature". Then you could just use Countif().

You'd have to do this in steps:
1) Change "Early_Mature" to "E_M"
2) Change "Semi_Mature" to "S_M"
3) Change "Mature" to "Fully_Mature"
4) reverse of step 1).
5) reverse of step 2).

answered May 31, 2018 at 14:52

MikeK

681 silver badge8 bronze badges

Collectives™ on Stack Overflow

Excel COUNTIF with strings, separated by comma

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related