COUNTIF not working

[sme527]

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!

[#2]

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().

[#3]

Thanks, Ed. I added the file to my post.

[#4]

@sme527

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

[#5]

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...

[#6]

@Haytham Amairah 

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

[#7]

@sme527

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.

[#8]

@Subodh_Tiwari_sktneer 

Thank you, this worked! MOST APPRECIATED!  Do you know how to make a the default setting such that I can convert the format of a group of cells to "real" "Number" format without going through the Data Tab? It is very misleading, since my Format --> Cells method has worked in the past...

Thank you,

Sarah

[#9]

You're welcome Sarah!

By default the format of the cells is General and it happens if you manually change the format of the cells to Text and then input the numbers in them and in that case they look like numbers but actually they are numbers as Text. If you input a number in a cell with General format, it will automatically aligned to the right which is a kind of visual clue that a number entered in the cell is not a real number if you haven't changed the alignment of the cell.

[#10]

@sme527

Glad to hear that the problem has been solved through Text to Columns.

Please note that the data formats in the Home tab not always represent the real format in the cell, especially when you try to change the format from Text to another format.

Data stored in text format sometimes contains some hidden characters that require some special transforms to be removed, so even when you try to change the format of the numbers to Number, the numbers stick with the Text format.

I recommend you enable the error indicators in Excel as described in the link below to alert you when the numbers are stored as texts, so you can convert them easily as described in my first reply.

https://support.office.com/en-us/article/hide-error-values-and-error-indicators-761401bc-25de-4673-8...

Regards

[#11]

@sme527 Note that formatting does not change the contents of a cell. It just changes how it looks. If you have 00345 in a cell that was formatted as text before you keyed it in, it will be text. If you change the cell format to number, it will NOT change it to a number. It leaves it as text. You can press F2/Enter and you'll see it then change to 345, as numbers don't retain leading zeros.

This is always an issue when you have numbers you want as text or text as numbers. You have to actually change the values in the cell. F2/Enter will do it one by one, the Data Tab will convert numbers to text, as will functions in another cell =TEXT() or VALUES() to convert to text/values respectively, then copy|Paste Values back to your original range.

If you weren't seeing the green triangle warning you of this issue, make sure this box is checked in your Excel options.