Counting instances of unique entries, with conditions

[ollyhughes1982]

Hi,

I previously put the below post on, and thought (at that point in time) that it had been solved:

Counting instances of unique entries, with conditions

The previous post read as follows:

Would anyone please be able to help with calculations in my ‘All Completed Runs - Piper’ worksheet, please? In my ‘All Completed Runs - Summary’ worksheet (cell B146) I need a count of the number of different events (venues) in each different country I have run in so far. The ‘All Completed Runs - pC-I.’ worksheet will form the basis for this calculation.

The definition of what is needed here is: ‘Run at 5 different events (venues) in each of 5 different countries (pC-Index of 5).’

I have populated in the separate worksheet (in yellow) what the results should be.

I think this will be some sort of unique filter type solution for the ‘All Completed Runs - Piper’ worksheet and possibly a sum product type solution for cell B146 of the ‘All Completed Runs - Summary’ worksheet, as has been the case in other similar areas of my workbook.

However, a few years down the line I have done more parkrun countries and realised that it needs a slight tweak. At the moment, it is simply picking up the first 5 countries I visited: UK, France, Poland, Finland and Germany. But, this isn’t correct. The logic is, that it should be picking up the 5 countries where I have done the most parkruns and in order by date first completed. If any of these are equal in number completed there, then select the earlier country by date first. e.g. I have done 1 parkrun in each of France, Finland, Norway, Sweden and Ireland, but I did France before the others, so I would like that to show as my fifth country. I have shown how it should look in the yellow tab, where I have manually entered the countries into column C.

A copy of the latest up-to-date file can be found here (small OneDrive file): Piper.xlsx

Thanks in advance!

Olly.

 

[ollyhughes1982]

This has now been resolved. I was able to resolve it by changing the sorting in the 'All Completed Runs - pC-I.' worksheet, so that it descends from the most in a country, to the least. I did this by using a combined formula for cells B4 and C4:

In B4:

=LET(u,IFERROR(UNIQUE(FILTER('All Completed Runs'!D4:D357,('All Completed Runs'!E4:E357<>"")*('All Completed Runs'!D4:D357<>"UK Crown Dependencies & UK Overseas Territories"))),""),c,COUNTIF('All Completed Runs'!D:D,u),SORTBY(HSTACK(u,c),c,-1))

...instead of the original:

=IFERROR(UNIQUE(FILTER('All Completed Runs'!D4:D357,('All Completed Runs'!E4:E357<>"")*('All Completed Runs'!D4:D357<>"UK Crown Dependencies & UK Overseas Territories"))),"")

and:

=IF(B4="","",COUNTIF('All Completed Runs'!D:D,B4#))

in B4 and C4, respectively.

Thanks.