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.

 

[Jan Kalop]

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

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

Works OK in different variations of random numbers, but still do not recognize "0" and count it as a digit 1-9
The same problem when implemented in B1 to sort A1 is OK, but when I try to adopt to my need ( value in BX827) and implemented cell CD827 I can not make it working.

 

[wrightyrx7]

The same problem when implemented in B1 to sort A1 is OK, but when I try to adopt to my need ( value in BX827) and implemented cell CD827 I can not make it working.

This bit is simple.

Change any of the formula that shows "A1" to "BX827" and paste into "CD827". Like so:

=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)-LEN(SUBSTITUTE(BX827,"-",""))+1)))-1)*LEN(BX827),LEN(BX827)))>0))

 

[markmzz]

Works OK in different variations of random numbers, but still do not recognize "0" and count it as a digit 1-9
The same problem when implemented in B1 to sort A1 is OK, but when I try to adopt to my need ( value in BX827) and implemented cell CD827 I can not make it working.

Sorry, but I didn't understand "still do not recognize "0"".

Look at this:

Layout

	A	B	BW	BX	BY	BZ	CA	CB	CC	CD
1	0-1-4-11-3-9-3	6
2	*
826	*
827	*			2-1-4-11-5-0-3						7
****	****************	****	**	***************	**	**	**	**	**	****

<TBODY>
</TBODY>

Formulas

In B1

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

In CD827

=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)-LEN(SUBSTITUTE(BX827,"-",""))+1)))-1)*LEN(BX827),LEN(BX827)))>0))

Markmzz

 

[Jan Kalop]

Sorry, but I didn't understand "still do not recognize "0"".

Look at this:

Layout

	A	B	BW	BX	BY	BZ	CA	CB	CC	CD
1	0-1-4-11-3-9-3	6
2	*
826	*
827	*			2-1-4-11-5-0-3						7
****	****************	****	**	***************	**	**	**	**	**	****

<tbody>
</tbody>

Formulas

In B1

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

In CD827

=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)-LEN(SUBSTITUTE(BX827,"-",""))+1)))-1)*LEN(BX827),LEN(BX827)))>0))

Markmzz

Yes I get to that point that on clean spreadsheet both formula (yours and wrightyrx7 ) working without any problems.
As soon as I get them on my spreadsheet, it seems that nothing works. What can be wrong.
And I still notice problem with "0". "0"' is still counted as a one of the digit 1-9

 

[Jan Kalop]

This bit is simple.

Change any of the formula that shows "A1" to "BX827" and paste into "CD827". Like so:

=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)-LEN(SUBSTITUTE(BX827,"-",""))+1)))-1)*LEN(BX827),LEN(BX827)))>0))

I was doing exactly the same, nothing seams to work on my spreadsheet.
On clean spreadsheets everything working fine except problem with "0" as a digit

 

[markmzz]

And I still notice problem with "0". "0"' is still counted as a one of the digit 1-9

Could you post a small example about the zero problem?

What you have and what you want.

Markmzz

 

[Jan Kalop]

My apology for confusion.

Cell were wrong formatted

Both formula working except problems with "0"

 

[Jan Kalop]

Sorry, but I didn't understand "still do not recognize "0"".

Look at this:

Layout

	A	B	BW	BX	BY	BZ	CA	CB	CC	CD
1	0-1-4-11-3-9-3	6
2	*
826	*
827	*			2-1-4-11-5-0-3						7
****	****************	****	**	***************	**	**	**	**	**	****

<tbody>
</tbody>

Formulas

In B1

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

In CD827

=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)-LEN(SUBSTITUTE(BX827,"-",""))+1)))-1)*LEN(BX827),LEN(BX827)))>0))

Markmzz

Could you post a small example about the zero problem?

What you have and what you want.

Markmzz

xxxxxxxxxxxxxxxxxxxxxxxxx

Like with your example in BX827 ......CD827 reads 7 but I would like exclude "0" and get final result 6. Would like not to count "0"

 

[markmzz]

Could you post a small example about the zero problem?

What you have and what you want.

Markmzz

xxxxxxxxxxxxxxxxxxxxxxxxx

Like with your example in BX827 ......CD827 reads 7 but I would like exclude "0" and get final result 6. Would like not to count "0"

Try this:

In CD827 - use Ctrl+Shift+Enter and not only Enter to enter the formula

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

Markmzz

 

[Jan Kalop]

Try this:

In CD827 - use Ctrl+Shift+Enter and not only Enter to enter the formula

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

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.