How to Use CORREL Function with Non-Contiguous Arrays

Your current formula will simply sum the three separate correlations, which probably isn't what you want. You can combine non-contiguous arrays into a range using an array formula, but you still need a way to distinguish which columns in the large array (Z37:AZ37) should be counted. Do all of the columns to be included share a header? Are they the only columns with numeric data?

For example, if the columns to be omitted are blank or contain text and the columns to be included are all positive values, you could use something like the following, array-entered (confirmed with Ctrl + Shift + Enter instead of Enter):

=CORREL(IF(Z37:AZ37>0,Z37:AZ37),IF(Z44:AZ44>0,Z44:AZ44))

It's hard to craft something more specific to your data without a sample workbook to look at. If you're still having difficulty, please post a sample with all private data altered or removed.

Here's an alternative that's hard-coded to specifically avoid columns 38,39, and 43-49. It should be array-entered:

=CORREL(IF(ISNA(MATCH(COLUMN(Z37:AZ37),{38,39,43,44,45,46,47,48,49},0)),Z37:AZ37),IF(ISNA(MATCH(COLUMN(Z44:AZ44),{38,39,43,44,45,46,47,48,49},0)),Z44:AZ44))

Maybe that'll work better?

Originally Posted by EverClever

I will update my results shortly.

Sounds good! A sample workbook would be a necessity if you're still having issues.

Excellent, glad to help. Thanks for the rep!