Counting rows with "" result

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
283
Office Version
  1. 365
Platform
  1. 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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows
I believe you just have to add 2 additional double quotes:
Rich (BB code):
=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,"<>""")
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
283
Office Version
  1. 365
Platform
  1. MacOS
I believe you just have to add 2 additional double quotes:
Rich (BB code):
=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 😂
 

Watch MrExcel Video

Forum statistics

Threads
1,132,912
Messages
5,655,917
Members
418,253
Latest member
TheJackal26

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
Top