Counting countries, with conditions

ollyhughes1982

Well-known Member
Joined
Nov 27, 2018
Messages
677
Office Version
  1. 365
Platform
  1. MacOS
Hi all,

Would anyone please be able to help with calculations in my ‘All Completed Runs - C EC C’ worksheet, please? In my ‘All Completed Runs - Summary’ worksheet (cells B136:B140) I need a count of the number of different countries run in so far (limited to the maximum for each category).

The definition of what is needed here is: ‘Run in X or more countries’

I have populated in the separate worksheets (in yellow) what the results for each should be. At the moment I have only done 4 different countries, so the same results are in each at the moment.

I think this will be some sort of unique filter type solution for the ‘All Completed Runs - C EC C’ worksheet and subsequent 4 worksheets and possibly a sum product type solution for cells B136-140 of the ‘All Completed Runs - Summary’ worksheet, as has been the case in other similar areas of my workbook.

Link to file here: Castle Classes.xlsx

Thanks in advance!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Hi. Please can someone let me know what I am missing here? I have the following formula in column A:

=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!D3:D2002,'All Completed Runs'!C3:C2002<>""),0),SEQUENCE(4)),"").

This seems to work, as it lists my unique countries in order, in column A. However, in column B I want to show the corresponding event that was completed for that first instance of a country, but it doesn't. I'm not sure what I'm missing. The formula I have at present is giving all of the first instances of the unique event, rather than the event corresponding to the first unique occurrence of the country: In column C I have:

=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!C3:C2002,'All Completed Runs'!C3:C2002<>""),0),SEQUENCE(4)),"")

I also need to use the same logic in columns C and D. i.e. the date and finish times that correspond to event for the first unique instance of a country.

The sequence is on there, as for this worksheet, I want to limit it to the first 4 unique countries.

In the separate small yellow table, below I have manually inputted what the entries should be

Please help! Thanks in advance.
 

Attachments

  • Screenshot 2020-04-26 at 17.48.32.jpg
    Screenshot 2020-04-26 at 17.48.32.jpg
    200.4 KB · Views: 11
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top