COUNTIF not working

[ollyhughes1982]

Hi, I wonder if anyone might be able to help, please.

I have a list of parkruns (and lots of other relevant details about each run) that I have completed, in the ‘All Completed Runs’ worksheet. This spreadsheet is populated from the data that is manually entered into the ‘ALL COMPLETED RUNS ENTRY’ worksheet.

In the ‘All Completed Runs - Summary’ worksheet I have a summary about these runs.

I am trying to count the Assisted Runs in the yellow highlighted field (‘# of Assisted parkruns’ [B56]) of the ‘All Completed Runs - Summary’ worksheet. The Assisted runs are signified by a 1 (conditionally highlighted in pink) in the relevant row of the S column in the ‘All Completed Runs’ worksheet. If there are no 1s in the column, then I want the yellow highlighted cell (B56) to appear empty.

I have tried the following formula:

=IF(COUNTIF('All Completed Runs'!S3:S2002,"?*")=0,"",COUNTIF('All Completed Runs'!S3:S2002,"?*"))

However this doesn’t seem to work, it only seems to work for counting if there are letters/text in the S column, rather than if there’s a 1.

I have 2 assisted runs in there, so the result should be 2. If I was to take out these two entries (i.e. remove them from the ‘ALL COMPLETED RUNS ENTRY’ worksheet), I would want the cell to appear empty, rather than showing a 0.

Apologies for the file size (it will take around 40 seconds to open), but there are lots of links and I can’t really cut the file down, because of the interconnectivity of worksheets. My file can be found here:

My parkrun Record.xlsx

Thanks.

 

[Fluff]

Maybe

=IFERROR(1/(1/COUNTIF('All Completed Runs'!S3:S2002,"1")),"")

 

[ollyhughes1982]

Thanks. That works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ollyhughes1982]

Sorry, just one more thing. In the cell next to it (C56), I have an alternate display with an 'x' in front of the number. If I want to add this in, how would I do that please? So the result would display 'x2', rather than just '2'. Again, appear blank if there are none. Thanks again.

 

[Fluff]

You'd have to use your formula but change the ?* to 1

 

[ollyhughes1982]

Fantastic. Thanks again for your help. Brilliant, as ever

 

[Fluff]

You're welcome & thanks for the feedback.