Formula for number of same digits.

[serge]

Hi All,

I need help with a formula that will be able to find how many numbers within those 5 are made the 2 of the same digits, for example :
00,11,22,33,44,55,66,77,88,99.

My numbers are in:

A1= 02
B1= 11
C1= 26
D1= 32
E1= 44

i will be grateful if anybody would help me, Thank you. Serge.

 

[energman58]

=SUMPRODUCT(--(LEFT(A1:E1,1)=RIGHT(A1:E1,1),LEN(A1:E1)>=2))

Works for me - the last term makes sure that there are two characters there - your have to make sure that the cells are formatted as text and not numbers to get 00 in there though as if it is a number 00 becomes 0 and isnt recognised as meeting your rule.

 

[serge]

Thanks for reply,

The formula does not work with me even after formatted as Text ? What I do wrong ?

Also, I need those numbers to be as number because my others formulas take those same numbers as references and if I change them They wouldn't work !!!

Thank you I will see how I can go around it.

 

[energman58]

There is a bracket missing in the formula somehow - sorry. I also took the chance to make the second term a tiny bit more robust

It should say:

=SUMPRODUCT(--(LEFT(A1:E1,1)=RIGHT(A1:E1,1)),--(LEN(A1:E1)>=2))

I cant think of a way to get around the problem with having to have it as text because even if you format the cells to display 0 as "00" using custom formats excel still sees it as 0.

You can use the formula VALUE to turn text that looks like a number into a number though so if you have somewhere you can put an intermediate value which is the number value of your text input that would work.

However, using the function TEXT to do the reverse - ie turn "00" into text that looks like 00 doesnt work as it evaluates as "0" as excel is still carrying it as 0 (rather than 00) as a number.

If you can be sure that 0 always means "00" in your input (after all 0=00=00000) then you could just add a count of the number of zero values in your range to the output of that formula above like this:

=SUMPRODUCT(--(LEFT(A1:E1,1)=RIGHT(A1:E1,1)),--(LEN(A1:E1)>=2)) + Countif(A1:E1,"=0")

And this would mean you could keep the numbers as numbers rather than text

Sorry not to have an easy solution for you!

 

[serge]

energman58,

Thanks very much for your quick and accurate response,Works great!!

Thanks again.

 

[energman58]

You are welcome

 

[pgc01]

Hi Serge

Try also:

=SUMPRODUCT(0+(MOD(A1:E1,11)=0))

 

[energman58]

PGC

I considered this but it will count blanks too as zero divided by anything has a mod of 0 so i rejected it (you coudl fix that with a second isnumber term in the sumproduct). Any suggestions for the "00" problem?

Also do you have any idea why you need the extra "0+" term in there - I know you need it (or something to do a similar job) to make it work properly but have never understood the underlying reason why.

 

[pgc01]

Hi Serge

I considered this but it will count blanks too as zero ....

Yes, you are right. In your post, both in the description and in the example you show the cells with numbers. That's what the formula considers.

Also do you have any idea why you need the extra "0+" term in there - I know you need it (or something to do a similar job) to make it work properly but have never understood the underlying reason why.

In the formula I used the comparison operator "=". The result of the comparison will be a boolean, either True or False. You cannot add booleans but you can convert them to numbers (True to 1 and False to 0) and then add them.

To convert a boolean to a number you can do it

- explicitly, using Value()

- or automatically, using the boolean in an arithmetic operation, like adding 0 or multiplying by 1. In this case excel knows that an arithmetic operation needs numbers and will do the conversion automaticallly

 

[serge]

Thank you PGC01, for this other formula I can use it too, for 00 or empty cell.
also thank you for the explanation but to me you speak Japanese, I know what I need for my table to work but I only know how to make very simple formula, yours guys are way to complicated for me, That why this forum is super great with all the Excel gurus guys like you that help others.

Thank you all for the help. Serge.