What is this Formula doing

[RP123]

Hi all.

Can anyone help with this formula. =SUM(COUNTIF(A2,"*"&Mylist&"*"))

I use it to match data from a table to that in a spreadsheet.

What I would like to know is how the formula doing this? I know what it is supposed to do and the results are correct but how is it matching the same numbers?

Any help would be appreciated.

[#2]

@RP123 

The formula tests A2 against each member of MyList in turn to see whether it is contained within the string A1. The resulting array of counts is summed so that multiple matches within the list are recognised.

[#3]

Hi @Peter Bartholomew

Thanks for the reply. That makes sense and is really helpful.

The part that I would like to understand better is "*"&Mylist&"*". What is happening in the formula here?

[#4]

@RP123 

 The formula

= "*"&Mylist&"*"

is an array of text strings, each one preceded and followed by a "*".  The "*" is a wildcard that matches any substring so making ""*&in"&"*" a match for "string".  

[#5]

Hi.

Thanks for the explanation. So if I've understood correctly, the whole formula is saying-

Total all the times that a number from Mylist is counted in the range starting from A2, including wildcard selections?

Would that be a way of explaining it? Or have a missed something?

Once again, your help is greatly appreciated.

RP