CORREL function with multi criterias

Hi all,

I am trying to evaluate correlation between two columns with:
- 1 criteria on first column
- 1 criteria on second column
- 1 criteria depending on another column

Example below:
2019-05-06_14-52-57.jpg

I want to use correl function for question 1 and question 2, keeping only values of question 1 and 2 below 11 and non null + only rows with "France" in column H.

I tried several options but did not manage to make it work with all 3 criteria. For instance, it is working with two criterias (below 11 for Q1 and below 11 for Q2) using this: =CORREL(IF(F2:F8<11;F2:F8;"");IF(G2:G8<11;G2:G8;""))

Thank you a lot for your help!

Originally Posted by KOKOSEK

try this:

Formula:

Please Login or Register  to view this content.

accept with Ctrl+Shift+Enter (not just Enter).

Result the same as canapone's formula

Hi Kokosek,

Thank you so much. It does work! Canapone's answer was fine too, but adding the criteria <>"" is important since it avoids counting nulls as 0.

Best,

Guillaume