Forum Discussion
COUNTIF not working
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
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.
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
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.
