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

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.