The CORREL function is used to find the correlation between two arrays. Because CORREL ignores text, you can use an array formula as one of the arguments of CORREL to limit it to a subset of the data.
To demonstrate, we’ll need some data. I fired up QuickSampleData to make fifty rows with a continent name in the first column and a two-digit number in the second.
Next I created the second array using RANDBETWEEN to give me data that had a relatively high correlation.
The formula needed to limit the correlation to Africa, with “Africa” in B1, is:
Enter that with Control+Shift+Enter, not just enter and Excel will put the curly braces in for you. I only needed to turn one of the arguments to text to get CORREL to ignore that row.