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.
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.
Last edited by Glenn Kennedy; 11-25-2020 at 09:47 AM.
Power! is knowledge...............
Part 1
=SUMPRODUCT(--(D3:P3=E3:Q3)*(E3:Q3<>F3:R3))
Part 2 to follow!
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
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...
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
Perectly clear now. All I have to do is try to figure out how to approach it.
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.
That was GREAT!
THANK YOU SO,SO,SO much dear Glenn.
Best REGARDS....................!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
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.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
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)
Bookmarks