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
Thanks in advance for any advice!
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)

Aladin

Posted by Graeme on May 29, 2001 3:07 AM

Thanks Aladin. I'm snowed under with work at the moment, so I can't try it yet. I can't wait as it's going to save me lots of keystrokes.
Cheers,
Graeme