CountIF returning 0 for in Excel 2016 for Mac

Question

Hello everyone, 

Excel keeps returning 0 when I use the COUNTIF function. This has really been an annoying problem. I will provide a example with pictures so you can see it for your selves. Why is this happening? Please help. I'm not necessarily interested in alternatives to get the value. I'm interested in why is Excel doing this to me :( 

Here is the screenshot for the correct COUNTIF value of "No"

Answer 1

If using "*" gives the correct results and without using "*" does not, this means there are some additional characters. Using "Yes" will search for only Yes, while "*Yes*" will search for any string of which Yes is a part of (ie. " Yes", or "Yes ", "Ayes", etc.). 

To determine this will need to look at your excel file and in case you can upload the same.

Regards

Answer 2

Then more information is required in order to troubleshoot the issue, starting with;

• Which version of macOS are you using?
• What is the present version update level of your Office 2016 installation?
• Is this in all workbooks or only specific one(s)?
• Is this a new behavior? IOW, has the function worked properly in the past but now does not?
• Does this occur when the program is recently launched?
• Does restarting your Mac have any effect on the issue?

Please understand that nobody is accusing you of anything. We know nothing about you & all we know about your issue is what you disclose to us. We're merely offering what we know to be the most likely causes/explanations based on the limited information we have. Unfortunately, screen shots only indicate what you're seeing - They often provide little or no insight into why you're seeing it.

I can't speak for Amit, but I cannot reproduce the behavior you're describing... Any time I've used the function or currently repeated the steps you're taking the results have been accurate. Additionally, there have been no similar reports here where operation of the function was found to be at fault.

Answer 3

Hi Gui,

Do you need further assistance? You may upload the file to a public clould storage and share a guest/public link here. We can help you check the file and look into the issue further.

Regards,

Tim

Answer 4

The COUNTIF function only returns the correct value if I insert the stars "**" in the arguments. However, if I do not put the stars in the argument, the COUNTIF function does not return the correct value. 

It is so strange. I can assure you that there are no spaces in the arguments like you suggested. I've checked over and over again. It only works if I insert the stars "**"

Answer 5

Guess what? I am going to confuse everyone.

I used Original Poster Formula and even used his rows and columns:

I used the formula =COUNTIF(H9:H16, "NO") in the No's Column.

I used the formula =COUNTIF(H9:H16, "YES") in the Yes's Column

However, I was careful to start in Row 9.

AND, I clicked on the H header in Column H and formatted the entire column for "Text."

_________

Disclaimer:

The questions, discussions, opinions, replies & answers I create, are solely mine and mine alone and do not reflect upon my position as a Community Moderator.