COUNTIF "*" appears to be counting blank cells !

[terrydennis]

I have produced an Excel Workbook which has various macros within it – developed with the help of people on this superb website. However , I have noticed a quirk which I cannot understand and therefore cannot correct ! HELP PLEASE !

The workbook is a leaderboard system for my golfclub which has impressed everyone !

A filtered list of information is generated in a hidden worksheet and the data list is transferred (still under the control of the same macro) to another specific worksheet called “Div 1” into which peoples scores are added manually during the course of the competition.

There are a maximum of 70 records of name/golf club name/handicap/score. A leaderboard worksheet (used for TV screen display) accesses “Div 1” to display the information contained therein.

One expression which is used within the Leaderboard is a count of the number of Entrants in the competition and used the expression

COUNTIF(‘Div1’!B7:B76,”*”)



Which counts the number of separate sequences of characters. My question is this.

The value returned in my Leaderboard from this expression is 61 whereas the actual number that I see in the worksheet “Div1” is 36. If I manually delete the apparently blank cells from B43 to B61 , I get the correct answer !! WHY ? Can there be something in the apparently blank cells which is being seen as characters by the COUNTIF expression and how did it get there ?



Strangely , I have two more worksheets within the workbook , “Div2” and “Div3” , also containing lists of people , clubs and handicaps , produced by similar worded macros which always give the correct answer !



I am perplexed ! Your help would be appreciated and I can forward the Workbook to you if it would help.



Regards



Terry Dennis

 

[Somendra Misra]

Hi Terry,

Can you post the file with data of B7:B76 on Div1 sheet.

Regards,

 

[terrydennis]

Somendra

I was looking at the website and noticed that the reply that I sent you yesterday does not appear in the thread so I am sending it again.


My email yesterday said : -
I am taking the easy way out and sending the whole Workbook. The Workbook opens with an AUTOEXCE macro at the "Set up" sheet. You just need to press ESC to give you access to the various other sheets.
You will see the COUNTIF expression in cell D9 on the "Leaderboard" sheet. This refers to cells B7:B76 on the "Div 1" sheet.
I have unprotected all the sheets that are normally protected.

Thanks for your offer to have a look at it

Regards

Terry Dennis

 

[Somendra Misra]

I think your macro is not putting an exact blank in the cells. Can you suggest what macro is putting the values in Div1!B7:B76. When you do the check like =ISBLANK(B7) to B76 it produces FALSE for B7:B67, that mean something is there. If you press F2 and press Enter it emove it and make the cell Blank.

So first thing review your Macro.

Second: Try this formula =COUNTIF('Div1'!B7:B76,">*")

But make sure to check your macro.

Regards,

 

[terrydennis]

Hi Somendra

Firstly , thanks so much for your time. It is much appreciated and , with great delight , I have to tell you that your suggested modification to the COUNTIF expression worked perfectly. Such a simple mod but it does the trick.

To answer your question about the macro. It is the 'Transfer_All_entries' macro that puts information from cells B271:D331 of the 'Transfer Sheet' into cells B7:F76 of the 'Div 1' sheet.
There is also a little routine in that macro that removes zeros from the copied range and inserts spaces but , to be honest , I cannot remember my logic for putting that in ! But I would be interested why the cells below the transferred VISIBLE information are not blank.

Thanks again ; I can sleep soundly tonight !

Best regards

Terry

 

[Somendra Misra]

Hi Terry,

I had seen on Transfer sheet you have a IF formula which is filling the cell with a null string "" if found BLANK cells. Now count function counts "". That is the reason you are not getting the right result with your original formula.

So my formula will count only those values which are greater than a NULL String, which in you case will be names.

If you press F2 in those cells B7:B76 and just press enter your cells which seems to be blanks will actually become blanks and your formula will give correct result.


Regards,

 

[terrydennis]

Somendra

Thanks for that but what should the coding be to overcome the problem ?

Regards

 

[terrydennis]

Somendra

If my question was not clear enough , maybe I should try again !

In your last message , you said
"I had seen on Transfer sheet you have a IF formula which is filling the cell with a null string "" if found BLANK cells. Now count function counts "". That is the reason you are not getting the right result with your original formula."
I did not understand what you meant ! There is not an IF formula on the "Transfer Sheet". Do you mean an IF expression in the 'Transfer_all_entries' macro ?
The relevant sub-routine of the macro to which you refer is probably as follows

Dim Cell As Range
Range("E7:E76").Select
For Each Cell In Selection
If Cell.Value = 0 Then
Cell.Value = ""
End If
Next

If that is the sub-routine to which you are referring , what should the syntax of the sub-routine be so that it clears each cell.

Hope that you understand my question

Regards

Terry