1

I'm trying to write a function that counts duplicate entries in a character column.
(See column function in image below)

However, the function reports incorrect information for some characters.
For example, the character < doesn't count at all, and the character * counts everything.

Is there a way to force excel to count characters literally, and ignore all special behavior?

enter image description here

Giffyguy
  • 1,062

3 Answers3

2

Here is an alternative to COUNTIF:

=SUMPRODUCT(N(CODE([Character])=CODE([@Character])))

The logic of this formula is searching code of character instead of character itself.

Also here is an article on why I used the SUMPRODUCT function: How to count values by length

FezzikMontoya
  • 483
1

Since the key issue is, "I'm trying to write a function that counts duplicate entries in a character column".

Write this formula in Cell B2 and fill it downwards:

=SUMPRODUCT(LEN($A$2:$A$12)-LEN(SUBSTITUTE($A$2:$A$12,A2,"")))

Check the screen shot:

enter image description here

Rajesh Sinha
  • 9,403
0

If you want COUNTIF to handle a literal *, then search for it as ~* as explained here.
To search for a literal < (less than) symbol, search for it as =< as demonstrated here.

To get them to work in your COUNTIF formula, use the SUBSTITUTE() function.

=COUNTIF([Character],SUBSTITUTE(SUBSTITUTE([@Character],"*","~*"),"<","=<"))
Ben
  • 433