Share via

CountIF returning 0 for in Excel 2016 for Mac

Anonymous
2017-03-30T08:28:24+00:00

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"

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2017-04-10T11:01:37+00:00

    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

    4 people found this answer helpful.
    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-03-30T09:14:42+00:00

    Check for any blank spaces before / after "Yes" in H9:H16.

    If the formula =COUNTIF(H9:H16,"*Yes*") gives correct results you would definitely have blank spaces.

    Regards,

    Amit Tandon

    2 people found this answer helpful.
    0 comments
  2. Bob Jones AKA CyberTaz MVP 430.5K Reputation points
    2017-03-30T13:52:48+00:00

    Building on Amit's reply, IMHO it's unlikely to be a leading space because the text is flush left. If any of the Yes entries was preceded by a space it would be visibly shown as an indent from the left cell wall.

    However, as he suggested, the same result is caused if the Yes entry is followed by one or more spaces, but the test formula he supplied would not indicate it - you'd also have to do the same but with the * wildcard behind the word Yes. What happens if you just carefully re-enter one of the Yes values?

    If these responses don't help resolve the issue, or if it is evident in scenarios other than this example, please be certain to indicate your present version level of Office 2016 & your flavor of macOS, along with any additional descriptive details & examples you can supply.

    0 comments
  3. Anonymous
    2017-03-31T03:26:41+00:00

    There are absolutely no spaces in between. There is no mistake on my part at all. Excel just chooses when to works for COUNTIF. I can literally have the words: Yes, Yes, No, No, No, Yes and Excel will return 0 when I do COUNTIF(Range, "Yes") but it will return maybe 1 or 2 for "No". Sometimes it just completely counts the number wrong. For example, if I have 10 "No" and Excel will return one "No".

    0 comments
  4. Anonymous
    2017-03-31T04:02:28+00:00

    Can you upload your excel file to pinpoint the problem?

    Regards

    0 comments