Count if values in one range appear in another

[gazskeltz]

Hi,

I have a row of values (row).
I have another range of values that I want to use as a lookup (lookup).

How can I count how many cells in (row) appear in range (lookup)?

I hope someone can help...

Regards

Gary

 

[Dannyh1]

Hi,

How about something like

A2 = if(isna(vlookup(A1,$B$1:$B$10,1,false)),"",1)

then sum row 2

 

[gazskeltz]

Hi,

Thanks for your quick reply. I can see what you're getting at but if I clarify it might help.

Here's my row of values (Row)

A B C D E F G H J

Here's my lookup (its a range)

A C E
G I J

I want to look at (row) and give a count of all the values in (row) that appear in (range) and put the answer in an adjacent cell in (row).

The answer in the above example would be 5.

B,D,F and H don't exist in (Range).
"I" was not searched for.

I have 1000 rows to calculate.

Thanks for your help,

Gary

 

[Dannyh1]

Hi,

is

A C E
G I J

all in one cell or different cells?

 

[gazskeltz]

Hi,

a range of 6 cells 3x2.

Regards

Gary

 

[energman58]

And is A B C D E F G H J in one cell or 9 cells?

 

[barry houdini]

Try like this

=SUMPRODUCT((COUNTIF(range,row)>0)+0)

 

[energman58]

If your data row you want to test is I4:N4 and your range of things you are looking for is in i6:k7 then

=COUNT((MATCH($I$6:$K$7,I4:N4,0)))

Entered as an array formula (Control SHift Enter all together rather than enter) works too

 

[gazskeltz]

Hi,

Thanks both to energman58 and Barry Houdini.

Both solutions worked really well!

The row was 9 seperate cells and the range was a grid of cells.

Thanks you all for your excellent and quick help!

Regards

Gary