ollyhughes1982
Well-known Member
- Joined
- Nov 27, 2018
- Messages
- 677
- Office Version
- 365
- Platform
- MacOS
Hi, I wonder if anyone might be able to help, please.
I have a list of the unique parkrun events that I have completed, in the ‘Completed Unique E(V)’ worksheet.
In the second worksheet (‘All Completed Runs - BCD’), I have a list of events that I have completed which are now no longer taking place, as well as the first (or only) date that I completed them.
This particular parkrun challenge requires four of these to be undertaken, to complete the challenge. At the moment I have only completed 2 events that are now closed (parkrun de Mandavit and Churchfields Farm), so there are two entries in the second worksheet at present.
I have a third ’All Completed Runs - Summary’ worksheet, where I want to summarise how I have done in this challenge. I am using the formula (below) to try and get to '2 out 4', but I keep getting '2 out of 2'. Any idea where I have gone wrong for the second figure? i.e. How I can get it to count the 4 rows, even though they aren't all populated.
=SUMPRODUCT(('All Completed Runs - BCD'!A3:A6<>"")/COUNTIF('All Completed Runs - BCD'!A3:A6,'All Completed Runs - BCD'!A3:A6&""))&" out of"&" "&COUNTIF('All Completed Runs - BCD'!A3:A6,"<>")
My file can be found here:
MR Excel - Example.xlsx
Thanks.
I have a list of the unique parkrun events that I have completed, in the ‘Completed Unique E(V)’ worksheet.
In the second worksheet (‘All Completed Runs - BCD’), I have a list of events that I have completed which are now no longer taking place, as well as the first (or only) date that I completed them.
This particular parkrun challenge requires four of these to be undertaken, to complete the challenge. At the moment I have only completed 2 events that are now closed (parkrun de Mandavit and Churchfields Farm), so there are two entries in the second worksheet at present.
I have a third ’All Completed Runs - Summary’ worksheet, where I want to summarise how I have done in this challenge. I am using the formula (below) to try and get to '2 out 4', but I keep getting '2 out of 2'. Any idea where I have gone wrong for the second figure? i.e. How I can get it to count the 4 rows, even though they aren't all populated.
=SUMPRODUCT(('All Completed Runs - BCD'!A3:A6<>"")/COUNTIF('All Completed Runs - BCD'!A3:A6,'All Completed Runs - BCD'!A3:A6&""))&" out of"&" "&COUNTIF('All Completed Runs - BCD'!A3:A6,"<>")
My file can be found here:
MR Excel - Example.xlsx
Thanks.