# Finding the number of values in one range which are also in another range.

Posted by Graeme on May 28, 2001 8:24 PM

Hello folks,
I am a relative newcomer to spreadsheets and I haven't found a way to do this yet. I have 3 columns of numbers (GHI) and I want a formula or formulas which tell me how many of these(in one row) are also in another range (either ABC - return number in J, or in ABCDEF - return number in K). (FIRST RANGE ) ( 2ND ) Results
A B C D E F G H I J K
1 2 3 4 5 6 7 5 1 1 2
Cheers,
Graeme

Posted by Graeme on May 28, 2001 8:26 PM

Sorry, the columns didn't line up once I posted!

Posted by Aladin Akyurek on May 29, 2001 12:33 AM

Mehod 1

In J1 array-enter: =SUM(ISNUMBER(MATCH(G1:I1,A1:C1,0))+0)
In K1 array-enter: =SUM(ISNUMBER(MATCH(G1:I1,A1:F1,0))+0)

Note. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER).

Method 2

In L1 enter: COUNTIF(\$A\$1:\$C\$1,G\$1) [ copy this across to O1 ]
In L2 enter: COUNTIF(\$A\$1:\$F\$1,G\$1) [ copy this across to O1 ]

In J1 enter: =SUM(L1:O1)
In K1 enter: =SUM(L1:O1)