# 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&" "))

## Files

• 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)

## Files

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

Thank you
UDF also works but I was thinking of looking for a solution to the formula.
my regards

• 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.
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!