I've been using the formula =SUMPRODUCT(--(COUNTIF(A1:T1,A2:T2)>0)) for comparing two list's of numbers and getting a count for matching numbers, but when I try to add a third list I get "too many arguements for this function" error. Is there another way to write this so it will work for 3 or more lists of numbers? Also, If I want to have the matching numbers from each list highlighted in color, how do I add that to the function? Thanks
Last edited by ssjagger; 07-01-2011 at 07:58 AM.
Can you define exactly what you are trying to count... in words.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I've got several sets of numbers in rows A1:M1 down to A50:M50. I use =SUMPRODUCT function to see how many of the numbers in row A2:M2 match numbers in row A1:M1. I would like see how many matching numbers there are in 3 rows. ie: A1:M1, A2:M2, A3:M3 and to have the matching numbers cell have color. I've tried conditional formating with =sumproduct function, but it doesn't do what I'm looking for.
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Doing this will ensure you get the result you need!
Here's a sample of what I'm working on.
I am not sure I follow... maybe some expected results would be nice to see....
Did you try?
=SUMPRODUCT(--(COUNTIF(A1:O1,A2:O3)))
copied down?
Anyone else have any ideas how I can do this?
What was wrong with my suggestion. It is a little rude to ask for other ideas when you haven't addressed the suggestion and/or requests given.
Sorry, not trying to be rude. I have tried your suggestions but didn't get the results I was seeking. I have attached an updated file with what I am looking for highlighted in color. Your =sumproduct formula results showed which numbers in the second and third row matched numbers in the first row. The results I am seeking is to get a count of how many numbers occur/match in all three rows.
Try:
=SUMPRODUCT(--(ISNUMBER(MATCH(A1:O1,A2:O2,0))),--(ISNUMBER(MATCH(A1:O1,A3:O3,0)))) in S3
as far as conditionally formatting... it is, I think a bit more complicated...
First select entire range from A1:O50 and add New Rule (use formula to determine which cells to format) and apply formula:
=AND(COUNTIF($A2:$O2,A1),COUNTIF($A3:$O3,A1))
Click Format and choose colour.
Then click Ok, and Ok again to close.
Then select from A2:O50 and apply conditional rule:
=OR(AND(COUNTIF($A3:$O3,A2),COUNTIF($A4:$O4,A2)),AND(COUNTIF($A1:$O1,A2),COUNTIF($A3:$O3,A2)))
and choose colour and close.
Then select now from A3:O50 and apply conditional rule:
=OR(AND(COUNTIF($A4:$O4,A3),COUNTIF($A5:$O5,A3)),AND(COUNTIF($A1:$O1,A3),COUNTIF($A2:$O2,A3)))
Last edited by NBVC; 06-30-2011 at 10:23 AM. Reason: Added conditional formatting answer.
Perfect, thanks for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks