Some kind of match and count possibly required

[ollyhughes1982]

Hi all,

I have a worksheet that shows all of my parkruns ‘All Completed Runs’. From this data I want to identify whether all runs have been completed at my home run. This is one of the parkrun challenges - if you have completed every one of your runs at your parkrun that was your home run at the time then your are a parkrun Passionista! What I want is in cell B164 of the ‘All Completed Runs - Summary’ worksheet to show either ‘Yes’ or ‘No’ and in cell C164 either ‘Yes, [value from cell B55]’ or ‘No’. So if the answer is ’No’ then it always displays No, if the answer is ‘Yes’ then a yes in B164 and then ‘Yes’ followed by a comma, space and then the value from cell B55.

e.g. If the answer is ‘Yes’: ‘Yes’ in B164 and ‘Yes, 245’ in C164. | If the answer is ‘No’: ‘No’ in B164 and 'No' in C164.

For me, the answer is just a ‘No’, but I need to be able to deal with ‘Yes’. In the ‘All Completed Runs’ worksheet I have added a column (EH) which identifies the Home parkrun at the time (these can change as people move around etc.). So, basically, I need a formula that calculates whether the Event run that time matches the home parkrun at that point in time (i.e. the values in Columns C and EH match) and then if this is the case for all rows with an entry in column C then the answer is ‘Yes’. If even one doesn't match then it is a 'No' - it has to be every event run has been at the person's home parkrun at the time.

Link to file: parkrun Passionista.xlsx

(Only a small file, should easily open very quickly)

Thanks in advance,

Olly.

 

[ollyhughes1982]

I've been doing some research and perhaps a combination of a countif / exact function are what I need. I can't seem to get to work though.

I have attached a Microsoft OneDrive link, if that's better. parkrun Passionista.xlsx

Thanks again.

 

[ollyhughes1982]

I've now managed to solve this.

I used this formula: =IF(COUNTIF('All Completed Runs'!EI4:EI2003,1)=0,"",IF(COUNTIF('All Completed Runs'!EI4:EI2003,1)=B55,"Yes, "&COUNTIF('All Completed Runs'!EI4:EI2003,1),"No"))

Thanks.