Forum Discussion
COUNTIF Function Issue
Hello! I need some guidance.
I have used the countif function so many times. But for some odd reason, everytime i use the function the answer keeps being 0 when in fact i know if should be more. On the same sheet i tested out with other random words and the countif worked but with my data it doesn't.
The excel spreadsheet i am using is a reporting i pulled from a work portal and downloaded in excel. I don't know why i am having this issue.
5 Replies
You may be attributing to us a lot of magical abilities if you're expecting a diagnosis without a bit more data.
- What is the nature of the data you've downloaded?
- Text? Numeric? Numeric but displayed as text?
- What is the formula as you've written it out?
- In particular, the criteria?
- Recall here the distinction between text, numeric and numeric as text.
My guess is that the issue has to do with assumptions you're making about the nature of the data, but it's just a guess.
- Its a reporting of submitted medical school interview evaluations from the committee that is submitted through our portal. The data consists of student names, offered accepted dates, evaluator names, evaluators decision (high accept. medium accept, low accept, high alternate list, medium alternate list, and low alternate list), and final eval score.
I am currently working on to count how many (high accept. medium accept, low accept, high alternate list, medium alternate list, and low alternate list) decisions did the 5 evaluators made. And everytime i use the countif function (ex: =countif(A3:J3,"low accept") it give me zero and i clearly see there are more. i tried adding space, capitalize each word, combine it - everything and still 0.
I tested out by typing high accept. medium accept, low accept, high alternate list, medium alternate list, and low alternate list - in each cell and used the countif function and it worked. But not sure why its not working on the data i downloaded from the portal.
i would greatly appreciate your assistance. Thank You!As Detlef_Lewin suggests, you have "bad data" which can mean a lot of things.
What you see as a space may be a special character that looks like a space, but isn't, so Excel sees it as not matching your entry of "low accept" as the criterion. Or -- this kind of thing happens a lot -- the downloaded data may have a trailing space after the words; again, to your eyes and mine, they look the same, but Excel is very literal, and "low accept" is not equal to "low accept "
So look for things like that in your data.
- What is the nature of the data you've downloaded?
