Count

[Jan Kalop]

how to count how many numbers is in cell with value like that:


12-3-4-11-5-9-3


I am looking for result =6 because in that cell is seven numbers separated by dash, but number 3 is listed twice.

 

[Aladin Akyurek]

how to count how many numbers is in cell with value like that:


12-3-4-11-5-9-3


I am looking for result =6 because in that cell is seven numbers separated by dash, but number 3 is listed twice.

Yes thanks, numbers are separated by "-" and your formula works fine, but giving as result "7" which is TRUE but I am looking for result "6", because number 3 is twice in that 12-3-4-11-5-9-3 value.
Would be perfect if in situations when zero "0"is part of that value, and could be excluded, and not count as a number.

If you are willing to add the following generic udf's in a module, using Alt+F11, to your workbook:

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

We can invoke:

=SUM(IF(FREQUENCY(IF(ISNUMBER(1/V(eval("{"&SUBSTITUTE(A2,"-",",")&"}"))),
  MATCH(V(),V(),0)),MATCH(V(),V(),0)),1))

which you need to confirm with control + shift + enter, not just enter.

 

[markmzz]

FANTASTIC.....!!!!


My apology for my mistakes which make a little bit more difficult for you to help me, but finally everything is prefect.

Thankyou all for help.

No problem and thanks for the feedback.

Here you have another way (use only Enter to enter the formula):

In CD827

=IFERROR(SUMPRODUCT(--(FREQUENCY(--MID(SUBSTITUTE(BX827,"-",REPT(" ",LEN(BX827))),1+(ROW(INDIRECT("1:"&(LEN(BX827)-LEN(SUBSTITUTE(BX827,"-",""))+1)))-1)*LEN(BX827),LEN(BX827)),
--MID(SUBSTITUTE(BX827,"-",REPT(" ",LEN(BX827))),1+(ROW(INDIRECT("1:"&LEN(BX827)))-1)*LEN(BX827),LEN(BX827)))>0))-ISNUMBER(FIND("-0-","-"&BX827&"-")),0)

Markmzz