Count criteria in one cell

  • I have specific conditions.
    How to count a specific criterion in a cell is an example attached.
    I entered the expected results.
    I have tried the formula, but it is not the correct result for all conditions


    =IF(ISNUMBER(LEFT(B$1;1)*1);(LEN($A2)-LEN(SUBSTITUTE($A2;B$1;"")))/LEN(B$1);SUM(LEN(" "&SUBSTITUTE($A2;" ";" ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE($A2;" ";" ")&" ";" "&B$1&" ";"")))/LEN(" "&B$1&" "))

  • Re: Count criteria in one cell


    Is a UDF solution acceptable?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Count criteria in one cell


    If UDF acceptable the try the attached.


    The Function in a standard module is

    Code
    Function CriterionCount(r As Range, r2 As Range) As Integer
        Dim x, i As Integer
        
        x = Split(Replace(Replace(r, "+", " "), "-", " "))
        For i = 0 To UBound(x)
            If CStr(x(i)) = CStr(r2) Then CriterionCount = CriterionCount + 1
        Next
        
    End Function


    and the formula in B2, dragged across and down, is


    =CriterionCount($A2,B$1)

  • Re: Count criteria in one cell


    You're welcome, I did try to develop a standard formula but could not get one that worked for all scenarios.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Count criteria in one cell


    Quote from KjBox;795125

    I did try to develop a standard formula but could not get one that worked for all scenarios.

    It does not matter.
    In any case, thank you for your answer and VBA.
    It's just a confirmation that the big problem is to create a formula for all scenarios. (If you could not then I certainly can not)

  • Re: Count criteria in one cell


    Hi KjBox
    I've found a way to solve the problem in any anticipated scenarios.
    The formula is next:


    =(LEN("#"&SUBSTITUTE(TRIM(" "&"#"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2;" ";"##");"+";"##");"-";"##");"(";"##");")";"##")&"#");" ";"##")&"#")-LEN(SUBSTITUTE("#"&SUBSTITUTE(TRIM(" "&"#"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2;" ";"##");"+";"##");"-";"##");"(";"##");")";"##")&"#");" ";"##")&"#";"#"&B$1&"#";"")))/LEN("#"&B$1&"#")


    btw: I use a semicolon as a separator instead of a comma

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!