Count same characters in a string

1. Count same characters in a string

Good day to all of you.
It is very difficult t explain with words So please take a look at the attachment.Thank you.

SORRY WRONG SECCTION. I NEED A FORMULA TO DO THESE.

2. Re: Count same characters in a string

Part 1

=SUMPRODUCT(--(D3:P3=E3:Q3)*(E3:Q3<>F3:R3))

Part 2 to follow!

3. Re: Count same characters in a string

I dont really understand what you want in Part 2. This delivers your expected answers, but I suspect it is more complicated!!

First bit:

=SUMPRODUCT(IFERROR((COUNTIF(D18:R18,D18:R18)>1)*(D18:R18<>"")/(COUNTIF(D18:R18,D18:R18)*(D18:R18<>"")),""))

Second bit:

=SUMPRODUCT((COUNTIF(D26:K26,D26:K26)>1)/COUNTIF(D26:K26,D26:K26))

The first bit is an array formula.
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

4. Re: Count same characters in a string

Sorry about that.My ENGLISH is not quit good in order to give you the exact description.
i'll try one more time for the second one.(The first its OK!)
i HAVE data of different lenth of columns every time.I need to check the pairs of that data and count them.
For example today i have data from A1:O1 (Tats 15 columns)
Now i need to check those pairs(A1,O1).(B1,N1).(C1,M1).(D1,L1).(E1,K1).(F1,J1)and finally(G1,I1)THE (H1) stay away becouse it has no pair
for that column.
Next day i might have 12 columns lenth of my data so i need a TYPE to find those pairs matching and counting.
THATS ALL my dear and thank you very much for your help
Regards Fulldeen

5. Re: Count same characters in a string

Perectly clear now. All I have to do is try to figure out how to approach it.

6. Re: Count same characters in a string

OK. Here it is. The orange coloured cell (X17) shows the starting point for the bigger, generic formula. I left it there, just in case I've made a mistake.

This array formula does the job:

=SUMPRODUCT(--(INDEX(D18:T18,N(IF(1,ABS(ROW(INDIRECT("1:"&ROUNDDOWN(COUNTA(D18:T18)/2,0)))-(1+COUNTA(D18:T18))))))=INDEX(D18:T18,N(IF(1,ROW(INDIRECT("1:"&ROUNDDOWN(COUNTA(D18:T18)/2,0))))))))

It is generic, working for both odd and even numbers. If you need it to work on more than 17 columns, adjust the ranges appropriately. I've given it about 5 minutes of testing and it seems OK. Don't forget that array formulae need CTRl-SHIFT-Enter, as described above.

7. Re: Count same characters in a string

That was GREAT!
THANK YOU SO,SO,SO much dear Glenn.
Best REGARDS....................!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

8. Re: Count same characters in a string

Thank goodness. You have no idea how much time I spent on this!!

I'm pleased that ou're happy with it, so you're welcome.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1