0

I have created a sample sheet at https://docs.google.com/spreadsheets/d/1tzykNfURTm5RG91fQ7oF6kX0tGdwT8koRz7DKQ8Dosg/edit#gid=0.

Column A is formatted as Plain text.

When considered numbers then 1.1 and 1.10 are equal. But when considered text, they are not.

However, COUNTIF seems to consider them numbers even if it is formatted as a text.

enter image description here

Ultimately, I want to use this formula in a data validation to ensure the same ID is not added twice. As such, I don't think I can use ARRAYFORMULA.

IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89

2 Answers2

1

Append apostrophe "'" before the cell value in the 2nd parameter of =COUNTIF(). The apostrophe indicates that the cell is formatted to text and force functions to recognize it as a text only.

=COUNTIF(A2:A, "'"&A2)

Example:

enter image description here

enter image description here

Nikko J.
  • 5,319
  • 1
  • 5
  • 14
-1

As already answered here: https://stackoverflow.com/a/70602903/14678267

If you are certain that the values that look like numbers are text, or if you want to check if any that look like numbers are text, then you can use COUNTIF formula like this:

=COUNTIF(A18:A,"?*")

enter image description here

Mr Shane
  • 520
  • 5
  • 18
  • I am not looking to count cells that contain ANY text. I am looking to COUNTIF a range where cell value is equal to a string when the range contains text that looks like a number. – IMTheNachoMan Jan 07 '22 at 18:54
  • the formula i provided counts all the cells that have numbers, but are formatted as text, that is why it is COUNTIF, otherwise it would not work, but it does, yet you marked this answer down. maybe there is misunderstanding with your explanation. – Mr Shane Jan 08 '22 at 03:12
  • I want to count how many times `value` is in a column when both `value` and column have text that looks like numbers. Yours only counts the # of values in column. I want to count the number of values in column that match `value. – IMTheNachoMan Jan 09 '22 at 03:58