Finding matching data

[solnajeff]

I have a spreadsheets where I have several thousand lines of data each containing 5 values in consecutive columns.

I wish to count the number of matching occurrences for each line with a larger value series (20 values).

Currently I am using individual formulas (countif) to do this but am sure there must be an easier way.

I would appreciate any comments.

Many thanks

Jeff

[shg]

Can you post a sample set of data and show desired results?

[solnajeff]

My apologies for uploading the wrong file

I thought it might be possible with DCount but did not have any luck in getting it to work.

It seems that it is the sort of thing that would crop up fairly often, but could not find any way to simplify the formula.

I should mention that the main reason for asking is that the subset is likely to expand to around thirty values and the main set to five hundred and this would make adding countif functions a bit of a problem.

Thanks again

Jeff

[shg]

=SUM(COUNTIF($A$1:$AT$1, A5:E5)), entered as an array formula (i.e., confirmed with Ctrl+Shift+Enter instead of just Enter)

[solnajeff]

Thanks a lot, will hopefully simplify things in future.

[shg]

Great, Jeff, good luck.