Exact matches when using COUNTIFS

[katel99]

Hi there,

I am trying to use the COUNTIFS funtion to highlight duplicate rows.

Here is an example of my data:

_____A _________B _____C_______D
1 UserID _____Name __Licence__Duplicate?

2 ABC1 ..............Joe ........1CL** ......=COUNTIFS(A:A,$A2,C:C,$C2)

3 ABC1...............Joe.........1CL*2........

4 ABC1...............Joe.........1CL**

5 BCD1...............Amy........1CL



As per the above, I would expect the result to tell me that there are 2 matches for rows 2 and 4, however because of the * in the "Licence" column (column C) my countifs formula is saying there are 3 matches (as it is assuming the * is a wild card, and is including row 3 in it's count)

Unfortunately I have no control of the *s in the data set, so I am wondering if it is possible to add something to the countifs formula to stop it using the * as a wild card? The above data set is very basic, there are multiple licence types I am comparing and I want this to be a repeatable process, so using "1CL**" is not an option.

Thanks in advance,

Kate

 

[jmacleary]

One way might be to insert a helper column. In column D have: =SUBSTITUTE(C2,"*","#") and in column E have: =COUNTIFS(A:A,$A2,D:D,$D2)

This assumes your licence values don not have any # character in them. If they do, replace the # in the formula with an unused character.

 

[katel99]

That's a great shout, thank you! Had been going a bit cross eyed and couldn't see the wood from the trees.

 

[jmacleary]

You're welcome and thanks for the feedback.

 

[Domenic]

For your second criteria, you can use the SUBSTITUTE function to replace each occurrence of an asterisk (*) with a tilde and asterisk (~*)...

=COUNTIFS(A:A,$A2,C:C,SUBSTITUTE(C2,"*","~*"))

Hope this helps!

 

[jmacleary]

For your second criteria, you can use the SUBSTITUTE function to replace each occurrence of an asterisk (*) with a tilde and asterisk (~*)...

Nice one, Domenic - more elegant than mine.