Forum Discussion
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.
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
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.
- Hi PeterBartholomew1
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?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".
