Forum Discussion
COUNTIF not working
I have a list of 25 numbers in one column (range). There is one "0" in the list.
In another cell when I use =COUNTIF(range, "=0"), I get a value of "1" as you would expect.
HOWEVER, when I use =COUNTIF(range, "<1"), I get an output value of "0" rather than "1" which is inaccurate.
This is not the only problem. For example, when I input =COUNTIFS(range, ">1", range, "<=3" I am also not getting the expected value.
I have already worked with Microsoft to update Excel and when that didn't work, we reset Excel, but that did not help. From other forums I learned that I needed to make sure the cells are formatted as "General" or "Number" rather than "Text," which I have also done, but to no avail.
Please advise.
Thank you!
10 Replies
Hi,
Please notice the green triangle in the top-left corner of each cell in the range!
This triangle indicates that the values of the cells are stored as text.
You can convert them to numbers in several ways, one of them: highlight the range, and select Convert to Number from the pop-up menu.
Hope that helps
PLEASE, I see my case was "closed" but it has not been resolved! Please see my last reply!!! The file I have does not have the cells formatted as "text" -- they are formatted as "Number" and it is still not working. Perhaps they converted to "text" when I sent it? But, if you look at the screenshot I sent, you will see, my file is accurately formatted and the function is not working. Please "reopen" my case. Or, please let me know how I can go about doing so. As I indicated in the first post, I had already ensured my formatting was accurate....
Thank you,
sme527
Thank you Haytham Amairah. However, on my file, I do not have the "green triangles" and the cells are formatted under Format --> Cells --> as "Number" as you can see in the image attached. It is still not working...
To convert the numbers entered as Text into real numbers, select column A --> Data Tab --> Text to Columns --> Finish.
To check if a number in a cell is a real number (not entered as Text), in any blank cell type the formula =ISNUMBER(A4)
If the formula returns TRUE, that means 4 in A4 has been converted into the real number.
- You'll need to share your workbook. Your syntax is just fine, so something else is going on.
Side note: I'd recommend using COUNTIFS() vs COUNTIF(). They both do the same thing for 1 criteria, but with COUNTIFS() you can add additional criteria. If you start with COUNTIF() and want to add more, you have to change the function and the order of your parameters. Same with SUMIFS(). Never use SUMIF().Thanks, Ed. I added the file to my post.
