Count if values in one range appear in another

gazskeltz

New Member
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

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Dannyh1

Well-known Member
Hi,

How about something like

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

then sum row 2

gazskeltz

New Member
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

Well-known Member
Hi,

is

A C E
G I J

all in one cell or different cells?

gazskeltz

New Member
Hi,

a range of 6 cells 3x2.

Regards

Gary

energman58

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

barry houdini

MrExcel MVP
Try like this

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

energman58

Well-known Member
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

New Member
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

Replies
6
Views
183
Replies
13
Views
808
Replies
3
Views
162
Replies
4
Views
193
Replies
6
Views
274

Threads
1,191,165
Messages
5,985,033
Members
439,935
Latest member
Monty238

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back