# Counting rows with "" result

#### ollyhughes1982

##### Active Member
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
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,"<>""")``

rows(a3:a6)

#### ollyhughes1982

##### Active Member
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

Replies
9
Views
85
Replies
7
Views
320
Replies
17
Views
646
Replies
26
Views
2K
Replies
8
Views
186

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?

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