Counting rows with "" result

[ollyhughes1982]

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.

 

[aRandomHelper]

I believe you just have to add 2 additional double quotes:

=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,"<>""")

 

[mart37]

rows(a3:a6)

 

[ollyhughes1982]

I believe you just have to add 2 additional double quotes:

=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,"<>""")

Yes, this worked perfectly! Thanks. I wasn't too far away ?