Hi all,

I have the below formula, but I need to add in one IF criteria to it. The formula basically lists if I have done a run of 7 different countries in consecutive parkrun weeks - which is working correctly. However, I now want to add in a criterion to ignore one particular country value - 'UK Crown Dependencies'.

=IF(ROW($A1)<=MAX('All Completed Runs'!$CQ:$CQ),INDEX('All Completed Runs'!D:D,MATCH(MAX('All Completed Runs'!$CQ:$CQ),'All Completed Runs'!$CQ:$CQ,FALSE)-MAX('All Completed Runs'!$CQ:$CQ)+ROW($A1)),"")

I need to only include values from 'All Completed Runs'!D:D which are not equal to ‘UK Crown Dependencies’. i.e. Ignore any country values that equal ‘UK Crown Dependencies’. This is because it is not a true country and needs to be ignored. In other formulas I have added in something like below, but am struggling to do this in this case:

'All Completed Runs'!D3:D5002<>"UK Crown Dependencies"

Thanks in advance,

Olly.

Attached Files

Example.xlsx (104.6 KB, 8 views)