Count

Jan Kalop

Active Member
Joined
Aug 3, 2012
Messages
389
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Are all the numbers separated by "-"

If so try this

=LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))+1
 
Upvote 0
Are all the numbers separated by "-"

If so try this

=LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))+1


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.
 
Last edited:
Upvote 0
Hi Jan,

Do you mind using VBA? You could create a "Function" that would work as you want it.

Code:
Option Explicit


Function UniqueCount(r As String) As Long
Dim v, s As String, i As Long
s = "~"
For Each v In Split(r, "-")
    If v <> 0 Then If InStr(s, "~" & v & "~") = 0 Then s = s & v & "~": i = i + 1
Next
UniqueCount = i
End Function

e.g. =UniqueCount(A1)
 
Upvote 0
is this for an assignment?


because the numbers could be

1 2 3 4 5 9

or

3 4 5 9 11 12
 
Upvote 0
Maybe this:

Code:
Use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUM(--(FREQUENCY(--MID(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),1+(ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))+1)))-1)*LEN(A1),LEN(A1)),
--MID(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),1+(ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))+1)))-1)*LEN(A1),LEN(A1)))>0))

Markmzz
 
Upvote 0
Yes maybe an assessment isn't the way to go...Maybe this then?

The below will not ignore the "0" though

=SUMPRODUCT(--(FREQUENCY(--TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",187)),187*(ROW(INDIRECT("1:"&1+LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1)+1,187)),--TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",187)),187*(ROW(INDIRECT("1:"&1+LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1)+1,187)))>0))
 
Upvote 0
This should ignore the "0"

=SUMPRODUCT(--(FREQUENCY(--TRIM(MID(SUBSTITUTE("-"&SUBSTITUTE(A1,"-0",""),"-",REPT(" ",187)),187*(ROW(INDIRECT("1:"&1+LEN(SUBSTITUTE(A1,"-0",""))-LEN(SUBSTITUTE(SUBSTITUTE(A1,"-0",""),"-","")))))+1,187)),--TRIM(MID(SUBSTITUTE("-"&SUBSTITUTE(A1,"-0",""),"-",REPT(" ",187)),187*(ROW(INDIRECT("1:"&1+LEN(SUBSTITUTE(A1,"-0",""))-LEN(SUBSTITUTE(SUBSTITUTE(A1,"-0",""),"-","")))))+1,187)))>0))
 
Upvote 0
This should ignore the "0"

=SUMPRODUCT(--(FREQUENCY(--TRIM(MID(SUBSTITUTE("-"&SUBSTITUTE(A1,"-0",""),"-",REPT(" ",187)),187*(ROW(INDIRECT("1:"&1+LEN(SUBSTITUTE(A1,"-0",""))-LEN(SUBSTITUTE(SUBSTITUTE(A1,"-0",""),"-","")))))+1,187)),--TRIM(MID(SUBSTITUTE("-"&SUBSTITUTE(A1,"-0",""),"-",REPT(" ",187)),187*(ROW(INDIRECT("1:"&1+LEN(SUBSTITUTE(A1,"-0",""))-LEN(SUBSTITUTE(SUBSTITUTE(A1,"-0",""),"-","")))))+1,187)))>0))



Great. In most scenarios working fine, but I notice that do not recognize "0" when is entered as a first digital.
Another problem for me is when I apply this formula in B1 to read A1 is working OK, but when I trying to apply to my scenario (BX827 as a cell with value ) I can not get the result in CD827.
 
Upvote 0
Great. In most scenarios working fine, but I notice that do not recognize "0" when is entered as a first digital.
Another problem for me is when I apply this formula in B1 to read A1 is working OK, but when I trying to apply to my scenario (BX827 as a cell with value ) I can not get the result in CD827.

Try this (a small modification in my formula of the post #6):

Code:
Use only Enter to enter the formula

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

Markmzz
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top