Counting instances of unique entries, with conditions

[ollyhughes1982]

Hi all,

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.

Link to file here: Piper.xlsx

Thanks in advance!

 

[ollyhughes1982]

Hi all. Wondered if anyone had any ideas re this. I have now added a helper column (the last column in the 'All Completed Run' worksheet) which lists the instance of each country and has helped me solve some of my other queries. I'm still stuck on this one though. I am struggling to find a way to specify finding the first country and then the first five events for that country, then the first five for the second country and then the first 5 for the third country and so on. I need to do it for the first five countries. Updated link is here:

Piper.xlsx

drive.google.com

Thanks in advance.

 

[ollyhughes1982]

Also posted on Excel Forum: Counting instances of unique entries, with conditions

 

[ollyhughes1982]

This is now resolved. Thanks.