Find correct patterns with "?" as a wildcard

**Hitch75** · 06-18-2010, 03:43 AM

Hi!
I need a formula which will return the number of correct patterns in column C, which correspond to each of the entries in column A.
In the patterns, the wildcard "?" substitutes any letter, but only one. For example, there are TWO patterns in column C which both correspond to the word GEORGE in column A; but none of the patterns corresponds to CHRIS (because CH???S has one character more).

It's easy to do it the other way round, but I need it this way... any ideas?

Example:
A .......................B (results) ........... C
ROBERT ............1 .........................A??X
CHRIS.................0 ........................GE?RG?
GEORGE............2..........................R?BERT
ALEX..................1..........................CH???S
......................................................?EORGE

**Andy Pope** · 06-18-2010, 03:50 AM

=sumif($a$2:$a$5,c2,$b$2:$b$5)

**DonkeyOte** · 06-18-2010, 03:52 AM

Do you mean ?

B1: =SUMPRODUCT(COUNTIF($A1,$C$1:$C$5))
copied down

**Andy Pope** · 06-18-2010, 03:57 AM

Ooops! I see I was working the other way in terms of matching.

**Hitch75** · 07-05-2010, 10:37 AM

DonkeyOte, it works, but it's kinda heavy and slow...
is there any other way?

**DonkeyOte** · 07-05-2010, 11:12 AM

I wouldn't envisage the formula to be too horrendous unless you have very large range references ... please provide an example of your final formula.

Whenever you use SUMPRODUCT it's generally advisable to keep ranges as lean as possible.