Comapre numerical values in one colum with other columns

Hello Friends,

I am using Excel 2007. I have various columns(7 columns) that have id numbers. Some columns have 1000 rows some have 600 some over 1000 rows, so the number of rows for each column is different. What I want to do is find out how many id's are there that are common to all the 7 columns(that is count of id numbers that are present in all the columns). For example, Out of numbers 1, 2 and 3 only number 3 is there in all the columns then I should get a count of 1. Or if the same values that are in all the columns get highlighted , even that is fine. Please let me know how to do this. Thank You.

Roh

Hello newbieroh,

Welcome.
I come up to this formula:

Please Login or Register  to view this content.

Now, have in mind that this is only for the example file I attached (3 column example that starts at column A).
But you can adapt it easily to your 7 column file.

Let me know if you require further assistance.


Kind Regards,
FCarv

Hello FCarv,

Thanks a lot for the reply. But I that is not what I am looking for. I guess, I didnt explain it correctly.


candy 1 candy 2 candy 3
101 105 101
105 110 105
110 333 110
200 201
201 333
301
333


Just say students with various student id's have received 3 different candy's. I am trying to count how many students received all the 3 candy's. In this case since 105, 110 and 333 are in all the columns the count should be 3.
Let me know if you need any other info. Thank You.

Ok, I was reviewing this and come up to the following.

Please see the attached file. It has the formula with the solution.
But to be able to reach a solution I had to insert a column with the list of ID's. Hope you can adapt it this way.


Rgds,
FCarv

Here's how I would do it.

Here's the way I would do that.
HTH,
Wayne.