Count with If / Max

[ollyhughes1982]

Hi all,

Would anyone please be able to help with calculations in my ‘Completed Unique E(V)’ worksheet, please? In my ‘All Completed Runs - Summary’ worksheet (cell B117) I need a count of the number of unique events (venues) for the United Kingdom only. So, basically a formula that looks at column C in the Completed Unique E(V)’ worksheet to check if the country is ‘United Kingdom’ and then perform a count of the unique events in column B. The answer should be 43. I did manage to get this result myself, but what I couldn’t do was to get it to cap off and display 250 when 250 is reached (like what happens when caps are reached in cells B111:B116). I can't seem to get it to work with the extra country dimension.

The definition of what is needed here is: ‘# of unique events (venues) completed (up to 250) - UK Only’.

Link to file here: parkrun - My parkrun Record - WORKING.xlsx

Thanks in advance!

 

[jasonb75]

You just need to cap it with MIN, i.e.

=MIN(count formula, 250)

I did notice some of the formulas that you were doing capping when I was looking at your file for you earlier question, the way you have been doing it is far more complicated than it needs to be.

 

[ollyhughes1982]

Great. Thanks, that worked perfectly ??