 # What is this Formula doing

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.
4 Replies

# Re: What is this Formula doing

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.

# Re: What is this Formula doing

Hi @Peter Bartholomew

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

# Re: What is this Formula doing

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".

# Re: What is this Formula doing

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