Counting where instances begin with a character, with conditions

[ollyhughes1982]

Hi all,

Would anyone please be able to help with calculations in my ‘All Completed Runs - I Alphabet’ worksheet, please? In my ‘All Completed Runs - Summary’ worksheet (cell B147) 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 - Alphabet’ worksheet works in a very similar way.

The definition of what is needed here is: ‘Complete events (venues) that start with each letter of the alphabet (excluding X), that are not in my home country (United Kingdom).’

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 - I Alphabet’ worksheet and possibly a sum product type solution for cell B147 of the ‘All Completed Runs - Summary’ worksheet, as has been the case in other similar areas of my workbook.

Link to file here: International Alphabeteer.xlsx

Thanks in advance!

 

[samitnair]

Hello

Your have made the question a bit complicated to understand and I wont be able to download the file due to security reasons. As per my understanding you need to create a summary of events

[Sheet (All Completed Runs - Summary) - cell B147

=COUNTA('All Completed Runs - I Alphabet'!$B$3:$B$28)

Let me know if that works

 

[ollyhughes1982]

Hi. I already have a worksheet that does almost exactly the same, except that one looks at events from any country. However, for this one, it must be only countries lie outside the UK. I have attached images of what I have already in that one. Thanks

 

[samitnair]

Alright so do we have a reference/list which tells which country hosted this run?

Example abeerberg parkrun - UK

So if we have a list like that then we can reference it.

 

[ollyhughes1982]

It’s the column next to the event name, column D, in the same ‘All Completed Runs’ worksheet

 

[ollyhughes1982]

This is now solved, thanks. I found out that to add extra criteria, you simply bracket the relevant part of the formula and then insert a '*' in between that and the second criteria.