Counting, by years

[ollyhughes1982]

Hi,

Please see attached link, where I am trying to solve my last query relating to my parkrun spreadsheet. This one is essentially 4 separate worksheets but all doing essentially the same calculation, but for different amounts:

- In the ‘All Completed Runs - B Obs’ worksheet ('Bronze Obsessive'), I want to record all years (and the total amount) where I have done 30-39 parkruns in the year

- In the ‘All Completed Runs - S Obs’ worksheet ('Silver Obsessive'), I want to record all years (and the total amount) where I have done 40-49 parkruns in the year

- In the ‘All Completed Runs - G Obs’ worksheet ('Gold Obsessive'), I want to record all years (and the total amount) where I have done 50-54 parkruns in the year

- In the ‘All Completed Runs - P Obs’ worksheet ('Platinum Obsessive'), I want to record all years (and the total amount) where I have done 55+ parkruns in the year

I have manually entered in the data, so you can see what should be populated.

Link: parkrun - My parkrun Record (Obsessives).xlsx

Thanks!

 

[Fluff]

For bronze how about
=UNIQUE(FILTER('All Completed Runs'!AJ3:AJ2002,(COUNTIF('All Completed Runs'!AJ3:AJ2002,'All Completed Runs'!AJ3:AJ2002)>=30)*(COUNTIF('All Completed Runs'!AJ3:AJ2002,'All Completed Runs'!AJ3:AJ2002)<=39)))
and
=COUNTIF('All Completed Runs'!AJ3:AJ2002,A3#)

 

[ollyhughes1982]

Yep, that worked and seems to work with the adjusted ranges in silver as well, but something goes wrong in gold. It gives me 1774 in column B? Likely because I haven't achieved this one yet? What will the formula change to for Platinum, when it just needs to be greater than or equal to, but no upper limit?

 

[Fluff]

You can change the formula in A to
=UNIQUE(FILTER('All Completed Runs'!AJ3:AJ2002,(COUNTIF('All Completed Runs'!AJ3:AJ2002,'All Completed Runs'!AJ3:AJ2002)>=50)*(COUNTIF('All Completed Runs'!AJ3:AJ2002,'All Completed Runs'!AJ3:AJ2002)<=54),"None"))

 

[Fluff]

And for platinum
=UNIQUE(FILTER('All Completed Runs'!AJ3:AJ2002,(COUNTIF('All Completed Runs'!AJ3:AJ2002,'All Completed Runs'!AJ3:AJ2002)>=55)*('All Completed Runs'!AJ3:AJ2002<>""),"None"))

 

[ollyhughes1982]

Thanks ??

 

[ollyhughes1982]

That all worked perfectly. Thanks so much for all of your help with this. Much appreciated!

 

[Fluff]

You're welcome & thanks for the feedback

 

[ollyhughes1982]

Hi. Now that I have completed all of the solutions for the individual worksheets in my workbook, it has thrown out a few of my calculations on the 'All Completed Runs - Summary'. I wonder whether you might be able to help with these. There only seem to be around 7 that have gone astray. I have highlighted them in red and put what the result should be on the right-hand side.

Link: parkrun - My parkrun Record - WORKING.xlsx

Thanks again!

 

[Fluff]

For B81 try
=COUNTIF('All Completed Runs - Alphabet'!B3:B200,"?*")